We have covered some basic calculations in prior tutorials as they can be quite useful to create additional computed columns through our expressions and formulas. This is important to be able to do so as in most cases, the computed values are not stored in the database but rather, these values are typically calculated at the time of the query so that they reflect the most up to date data rather than depending on updating columns data as the data is calculated.
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)
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.
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
The result is set 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 pulling out just the number with the date_part function:
SELECT employee_id, hire_date, birth_date, date_part('day',hire_date - birth_date)
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
Rather, we’ll 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)
This would be a great example of the 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)
Give it a try and see what it comes up with. This would not be something 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.