Retrieving Data › Displaying Calculations in Columns

Displaying Calculations in Columns

You can use arithmetic expressions to calculate new values from a column.

Use the following symbols for arithmetic operations:

Order of Evaluation

Multiplication and division are performed first, from left to right. Addition and subtraction are performed second, from left to right. You can control the order in which operations are performed by using parentheses to enclose the operations you want performed first.

Computing with Null Values

Unless the column definition specifies otherwise, a column can contain no value. No value is also called null, or a null value. The result table usually shows null values as '<null>'.

The result of any calculation involving a null value is always a null value.

How It's Done

This year, the base rate for all jobs is rising 5 percent above last year's rates. The budget group needs a report showing job ID, last year's rate, and last year's rate plus 5 percent. This information is contained in the JOB table. To display the new rate, you will have to multiply the current rate by 1.05.

To create a table showing job ID, last year's rate, and this year's rate, enter:

select job_id as "Job", min_rate as "Minimum Rate",
       min_rate * 1.05 as "Adjusted Rate"
       from job;

You can omit the space on either side of the arithmetic symbol.

The result looks like this:

   JOB    MINIMUM RATE     ADJUSTED RATE
   ---    ------------     -------------
  8001        90000.00        94500.0000
  2077        17000.00        17850.0000
  9001       111000.00       116550.0000
  3051            8.50            8.9250
  4700        33000.00        34650.0000
  3029        25000.00        26250.0000
  6011        59400.00        62370.0000
  4130        35000.00        36750.0000
  4666        41000.00        43050.0000
  4123        35000.00        36750.0000
  5555        30000.00        31500.0000
  4025        31000.00        32550.0000
  4023        44000.00        46200.0000
  2051            8.80            9.2400
  4734        25000.00        26250.0000
  5110        40000.00        42000.0000
  2053            8.80            9.2400
  6004        66000.00        69300.0000
  5111        27000.00        28350.0000
  4012        21000.00        22050.0000
  2055        17000.00        17850.0000
  4560           11.45           12.0225
  5890        45000.00        47250.0000
  3333        21600.00        22680.0000
  6021        76000.00        79800.0000
25 rows processed

Why did you provide a heading for the calculated column?

You provided a heading to have a more meaningful name. If you hadn't, the heading would have been (EXPR) or Expression.