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 column with the results of the calculation appears in the report.
• When the expression is part of the WHERE clause, it is part of the search condition and does not alter column values.

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

• This query selects every employee's annual salary from the Q.STAFF table:

  SELECT ID, SALARY
  FROM Q.STAFF

• This query selects every employee's monthly salary, which must be calculated:

  SELECT ID, SALARY/12
  FROM Q. STAFF

  SALARY/12 is called an expression. It means the result of dividing SALARY by 12.

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             EXPRESSION 1
------ ---------  ------------------
   38  MARENGHI      1458.8958333333
   38  O'BRIEN       1500.5000000000
   38  QUIGLEY       1400.6916666666
   38  NAUGHTON      1079.5625000000
   38  ABRAHAMS      1000.8125000000

Arithmetic operators:

Operator

Operation

+

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.

Example:

• Select the name and total earnings (salary plus commission) of every employee who earns more than $20,000 a year:

  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 commission is null. The result of operating on an unknown is unknown.

• List anyone whose commission is 5% or more of their total earnings:

  SELECT NAME, SALARY, COMM
  FROM Q.STAFF
  WHERE COMM = 0.05 * (SALARY + COMM)