Export (0) Print
Expand All
This topic has not yet been rated - Rate this topic

Computed Values in the Select List

A select list can contain expressions that are built by applying operators to one or more simple expressions. This allows result sets to contain values that do not exist in the base tables, but are calculated from the values stored in the base tables. These result set columns are called derived columns, and include:

  • Calculations and computations that use arithmetic operators or functions on numeric columns or constants:
    SELECT ROUND( (ListPrice * .9), 2) AS DiscountPrice
    FROM Production.Product
    WHERE ProductID = 58
    
  • Data type conversions:
    SELECT ( CAST(ProductID AS VARCHAR(10)) + ': '
           + Name ) AS ProductIDName
    FROM Product
    
  • CASE functions:
    SELECT ProductID, Name,
       CASE Class
          WHEN 'H' THEN ROUND( (ListPrice * .6), 2)
          WHEN 'L' THEN ROUND( (ListPrice * .7), 2)
          WHEN 'M' THEN ROUND( (ListPrice * .8), 2)
          ELSE ROUND( (ListPrice * .9), 2)
       END AS DiscountPrice
    FROM Production.Product
    
  • Subqueries:
    SELECT Prd.ProductID, Prd.Name,
           (   SELECT SUM(OD.UnitPrice * OD.OrderQty)
               FROM AdventureWorks.Sales.SalesOrderDetail AS OD
               WHERE OD.ProductID = Prd.ProductID
           ) AS SumOfSales
    FROM AdventureWorks.Production.Product AS Prd
    ORDER BY Prd.ProductID
    

Calculations and computations can be performed with data by using numeric columns or numeric constants in a select list with arithmetic operators, functions, conversions, or nested queries. Arithmetic operators let you add, subtract, multiply, and divide numeric data.

The following arithmetic operators are supported.

Symbol Operation

+

Addition

-

Subtraction

/

Division

*

Multiplication

%

Modulo

The arithmetic operators that perform addition, subtraction, division, and multiplication can be used on any numeric column or expression: int, smallint, tinyint, decimal, numeric, float, real, money, or smallmoney. The modulo operator can only be used on int, smallint, or tinyint columns or expressions.

Arithmetic operations can also be performed on datetime and smalldatetime columns using the date functions or regular addition or subtraction arithmetic operators.

You can use arithmetic operators to perform computations involving one or more columns. The use of constants in arithmetic expressions is optional, as shown in this example:

SELECT p.ProductID, p.Name,
    SUM (p.ListPrice * i.Quantity) AS InventoryValue
FROM AdventureWorks.Production.Product p
JOIN AdventureWorks.Production.ProductInventory i
ON p.ProductID = i.ProductID
GROUP BY p.ProductID, p.Name
ORDER BY p.ProductID
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft. All rights reserved.