Online College Courses for Credit

+
Calculations in SELECT Statements

Calculations in SELECT Statements

Rating:
(0)
Author: Sophia Tutorial
Description:

Use calculations in SELECT statements to simplify resulting output on live data.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores using calculations in SELECT statements to create more complex data results.

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)
FROM invoice_line
GROUP BY invoice_id;

File:11463-2400-1.png

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
FROM employee;

File:11464-2400-2.png

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)
FROM employee;

File:11465-2400-3.png

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;

File:11466-2400-4.png

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)
FROM employee;

File:11467-2400-5.png

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)
FROM employee;

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.


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.

summary
We can have live calculations of data in the SELECT statements to simplify data.