Calculated results

You can use calculated values as part of a search condition. You can also display them for selected rows just as you display column values.

You can use an arithmetic expression in the SELECT clause or in the WHERE clause of the query:
  • When the expression is part of the SELECT clause, the new calculated column resulting from the expression appears in the report.
  • When the expression is part of the WHERE clause, it is part of the search condition; no new column appears in the report.

The following two queries illustrate the use of an arithmetic expression in a SELECT clause.

This query:

SELECT DEPT, NAME, SALARY
FROM Q.STAFF
WHERE DEPT = 38

Produces this report:

DEPT NAME            SALARY
---- ----------  ----------
 38  MARENGHI      17506.75
 38  O'BRIEN       18006.00
 38  QUIGLEY       16808.30
 38  NAUGHTON      12954.75
 38  ABRAHAMS      12009.75

This query:

SELECT DEPT, NAME, SALARY/12 
FROM Q.STAFF
WHERE DEPT = 38

Produces this report:

  DEPT  NAME                     COL1
------  ---------  ------------------
    38  MARENGHI      1458.8958333333
    38  O'BRIEN       1500.5000000000
    38  QUIGLEY       1400.6916666666
    38  NAUGHTON      1079.5625000000
    38  ABRAHAMS      1000.8125000000

You can use the following arithmetic operators in calculations:

+    Add
-     Subtract
*    Multiply
/     Divide

Within expressions, you can use column names (as in RATE*HOURS), columns and constants (as in RATE*1.07), and built-in functions (as in AVG(SALARY)/2). An expression can consist of numeric constants (such as 3*7) or character constants (such as SALARY + COMM).

When a table is created, each column in it is defined to hold a certain type of data. Arithmetic operations can be performed only on numeric data types, and the results of an operation can depend on the data types of the operands.

Examples:

  • To select the name and total earnings (salary plus commission) of every employee who earns more than $20,000 a year, use a query like the following: 
    SELECT NAME, SALARY + COMM
FROM Q.STAFF
WHERE SALARY + COMM > 20000

    The above query does not list anyone whose salary alone is greater than $20,000 when the amount of the commission is null, because the result of operating on an unknown is unknown.

  • To list anyone whose commission is 5% or more of their total earnings, use a query like the following: 
    SELECT NAME, SALARY, COMM
FROM Q.STAFF
WHERE COMM >= 0.05 * (SALARY + COMM)