Retrieving Information from a Table › Displaying Calculations in Columns

Displaying Calculations in Columns

Compute new values

The SELECT statement displays calculated values, in addition to values stored in the table. For example, you might want to calculate a 6% bonus for each employee.

To display a calculated column, include an arithmetic expression in the column list following the SELECT statement. An arithmetic expression uses these operators:

You can also use a built-in function to display a calculated column. Built-in functions are described later in this chapter.

Some examples

A few examples of arithmetic expressions appear below. These examples show you can:

• Leave spaces, or omit spaces, before and after these arithmetic operators: *, +, /. You must include a blank space before and after a minus (-) sign.
• Use parentheses to show how the arithmetic expression should be evaluated and to improve readability.

Provide a heading for a calculation

To give each calculated column a heading, use the keyword AS and a heading name following the arithmetic expression. Enclose headings with two or more words in single quotation marks.

Example

List each employee's salary and end-of-year bonus, based on 6% of salary:

select empid, salary as &xq.annual salary', salary * 0.06 as bonus
  from emp ! display

                                   EMP REPORT
                                    mm/dd/yy

            EMPID           ANNUAL SALARY                BONUS
            ─────           ─────────────           ───────────────
             0001                76000.00                 4560.0000
             0003                65000.00                 3900.0000
             0007                80000.00                 4800.0000
             0019                37000.00                 2220.0000
             0020                55000.00                 3300.0000
             0021                20000.00                 1200.0000
             0024                33000.00                 1980.0000
             0027                33000.00                 1980.0000
             0028                34500.00                 2070.0000
             0029                33000.00                 1980.0000
             0030               240000.00                14400.0000
             0035                37500.00                 2250.0000
             0120                18000.00                 1080.0000
             0127                18000.00                 1080.0000

                                      - 1 -