Presents your 
SQL SERVER E-NEWSLETTER for May 22, 2003

<------------------------------------------->

USING COMPUTED VALUES IN THE SELECT LIST

There are times when your queries may need to return a result set that
doesn't exist in a table but is the result of a mathematical calculation.
You can use computed values in your SELECT statement to create derived
columns. Derived columns can include simple calculations, data type
conversions, CASE functions, and subqueries.

In this query, we'll calculate the rounded price for the item with the
ProductID of 58 in the Products table of the Northwind database.

Use Northwind
SELECT ROUND( (UnitPrice * .9), 2) AS DiscountPrice
            FROM Products
            WHERE ProductID = 58

DiscountPrice
-----------------------
11.93000

(1 row(s) affected)

Next, we'll convert the ProductID int data type in the Products table to
a varchar data type.

Use Northwind
SELECT ( CAST(ProductID AS VARCHAR(10)) + ': '
              + ProductName ) AS ProductIDName
FROM Products

ProductIDName                                        
-----------------------------------------------------
17: Alice Mutton
3: Aniseed Syrup
. . .
64: Wimmers gute Semmelknodel
47: Zaanse koeken

(77 row(s) affected)

Now we'll select the ProductID and ProductName and calculate a 
different
discount price on each CategoryID.

Use Northwind
SELECT ProductID, ProductName,
             CASE CategoryID
                        WHEN 1 THEN ROUND( (UnitPrice * .6), 2)
                        WHEN 2 THEN ROUND( (UnitPrice * .7), 2)
                        WHEN 3 THEN ROUND( (UnitPrice * .8), 2)
                        ELSE ROUND( (UnitPrice * .9), 2)
             END AS DiscountPrice
FROM Products

ProductID            ProductName                         DiscountPrice
          
---------            -----------                          -------------
          
1                       Chai                               10.80000              
2                       Chang                              11.40000              
76                   Lakkalikoori                          10.80000              
77                Original Frankfurter grune
Sobetae          9.10000               
(77 row(s) affected)

You can perform calculations like the one above with data using numeric
columns and numeric constants in a SELECT list with arithmetic
operations, functions, conversions, or nested queries.

In the following example, we're looking for the ProductID, ProductName,
and SumOfSales from the Northwind database.

Use Northwind
SELECT Prd.ProductID, Prd.ProductName,
            (   SELECT SUM(OD.UnitPrice * OD.Quantity)
            FROM Northwind.dbo.[Order Details] AS OD
            WHERE OD.ProductID = Prd.ProductID
             ) AS SumOfSales
FROM Northwind.dbo.Products AS Prd
ORDER BY Prd.ProductID

ProductID            ProductName                    SumOfSales                   
---------            -----------                     ----------                   
1                     Chai                          14277.6000                   
2                     Chang                         18559.2000                   
76                   Lakkalikoori                   16794.0000                   
77                Original Frankfurter grune
Sobetae    9685.0000                   

(77 row(s) affected)

----------------------------------------