Computing values in the SELECT list

The expressions in the select list can be more complicated than just column names or strings. For example, you can perform computations with data from numeric columns in a select list.

Arithmetic operations

To illustrate the numeric operations you can perform in the select list, you start with a listing of the names, quantity in stock, and unit price of products in the SQL Anywhere sample database.

SELECT Name, Quantity, UnitPrice
FROM Products;
Name Quantity UnitPrice
Tee Shirt 28 9
Tee Shirt 54 14
Tee Shirt 75 14
Baseball Cap 112 9
... ... ...

Suppose the practice is to replenish the stock of a product when there are ten items left in stock. The following query lists the number of each product that must be sold before re-ordering:

SELECT Name, Quantity - 10
   AS "Sell before reorder"
FROM Products;
Name Sell before reorder
Tee Shirt 18
Tee Shirt 44
Tee Shirt 65
Baseball Cap 102
... ...

You can also combine the values in columns. The following query lists the total value of each product in stock:

SELECT Name, Quantity * UnitPrice AS "Inventory value"
FROM Products;
Name Inventory value
Tee Shirt 252.00
Tee Shirt 756.00
Tee Shirt 1050.00
Baseball Cap 1008.00
... ...
Arithmetic operator precedence

When there is more than one arithmetic operator in an expression, multiplication, division, and modulo are calculated first, followed by subtraction and addition. When all arithmetic operators in an expression have the same level of precedence, the order of execution is left to right. Expressions within parentheses take precedence over all other operations.

For example, the following SELECT statement calculates the total value of each product in inventory, and then subtracts five dollars from that value.

SELECT Name, Quantity * UnitPrice - 5
FROM Products;

To ensure correct results, use parentheses where possible. The following query has the same meaning and gives the same results as the previous one, but the syntax is more precise:

SELECT Name, ( Quantity * UnitPrice ) - 5
FROM Products;

See also Operator precedence.

String operations

You can concatenate strings using a string concatenation operator. You can use either || (SQL/2003 compliant) or + (supported by Adaptive Server Enterprise) as the concatenation operator. For example, the following statement retrieves and concatenates GivenName and Surname values in the results:

SELECT EmployeeID, GivenName || ' ' || Surname AS Name
FROM Employees;
EmployeeID Name
102 Fran Whitney
105 Matthew Cobb
129 Philip Chin
148 Julie Jordan
... ...
Date and time operations

Although you can use operators on date and time columns, this typically involves the use of functions. See SQL functions.

Additional notes on calculated columns
  • Columns can be given an alias   By default the column name is the expression listed in the select list, but for calculated columns the expression is cumbersome and not very informative.

  • Other operators are available   The multiplication operator can be used to combine columns. You can use other operators, including the standard arithmetic operators, and logical operators and string operators.

    For example, the following query lists the full names of all customers:

    SELECT ID, (GivenName || ' ' || Surname ) AS "Full name"
    FROM Customers;

    The || operator concatenates strings. In this query, the alias for the column has spaces, and so must be surrounded by double quotes. This rule applies not only to column aliases, but to table names and other identifiers in the database. See Operators.

  • Functions can be used   In addition to combining columns, you can use a wide range of built-in functions to produce the results you want.

    For example, the following query lists the product names in uppercase:

    SELECT ID, UCASE( Name )
    FROM Products;
    ID UCASE(Products.name)
    300 TEE SHIRT
    301 TEE SHIRT
    302 TEE SHIRT
    400 BASEBALL CAP
    ... ...

    See SQL functions.