Calculations in SELECT Statements

1. Keeping Queries Current

We have covered some basic calculations in prior tutorials, which can be quite useful to create additional computed columns through expressions and formulas. In most cases, the computed values are not stored in the database. Rather, these values are typically calculated at the time of the query so that they reflect the most up-to-date data.

For example, if we wanted to calculate the total amount by invoice_id in the invoice_line table, we would need a query like the following:

SELECT invoice_id, SUM(quantity * unit_price) 
FROM invoice_line
GROUP BY invoice_id;

This query would calculate the total of the invoices by adding up the product of the quantity multiplied by the unit_price per item. Although in this case, the value is also stored in the invoice table in the total column, the value in the invoice table is rounded up to the nearest integer.

2. Calculating Age Example

We can also do the same for a more complex query with dates to find the employee’s age when they were hired based on their birthdate. If we simply calculated the difference between the two dates:

SELECT employee_id, hire_date, birth_date, hire_date - birth_date 
FROM employee;

The result is based on the days. To convert this to a year, we would have to divide it by the number of days in a year. To include the potential leap years, we could divide the age by 365.25 after we’ve converted the number of days to a number, and pulled out just the number with the date_part function:

SELECT employee_id, hire_date, birth_date, date_part('day',hire_date - birth_date) 
FROM employee;

If we simply divided the value by 365.25 without the conversion, the result would be displayed in days instead of years:

SELECT employee_id, hire_date, birth_date, (hire_date - birth_date)/365.25 
FROM employee;

Instead, let's divide the number of days by 365.25 and then round it to the nearest integer:

SELECT employee_id, hire_date, birth_date, ROUND(date_part('day',hire_date - birth_date)/365.25) 
FROM employee;

This is a great example of calculation being used. You could even convert the hire_date to use now() to get the current date to find out the employee’s current age at the time that the query is run:

SELECT employee_id, hire_date, birth_date, ROUND(date_part('day',now() - birth_date)/365.25) 
FROM employee;

Give it a try and see what happens. This is not a calculation you could easily store in the database, as the value would constantly be changing. If it were to be stored, you would have to update the table every day. Consider that aspect of live data as we look ahead to the use of views.

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.