Ctrl+K

Aggregating and Calculating

Aggregating and Calculating#

SQL contains functions which allow you to make calculations on data in your database for reports.

Some of the most common functions include:

  • MAX: returns the maximum value in a field

  • MIN: returns the minimum value in a field

  • AVG: returns the average value of a field

  • COUNT: counts the number of values in a field and returns the total

  • SUM: adds the values in a field and returns the sum

Let’s say we wanted to get the average birth year for players in our dataset. We can use AVG in our query.

-- get average DoB from specific table
SELECT AVG(DoB)
FROM players;

We can filter the results of aggregate functions using the HAVING keyword.

  • NOTE: The HAVING keyword has to be used in combination with GROUP BY.

Let’s say we only wanted to see the average birth year for players born after 1990.

-- get average DoB from specific table only for records that meet a specific condition
SELECT AVG(DoB)
FROM players
GROUP BY LocationID
HAVING DoB > 1990;
Check out W3Schools "SQL Operators" page to learn more about SQL Operators, including arithmetic, comparison, compound, and logical operators.

In SQL, we can also perform calculations as part of a query. We can use expressions on a column or multiple columns to get new values during our query. The results of these calculations are known as computed columns.

SQL’s arithmetic operators include:

Operator

Description

+

Add

-

Subtract

*

Multiply

/

Divide

%

Modulo

For example, let’s say we had temperature data in Fahrenheit and needed those values in Celsius, rounded to two decimal places. We could make this conversion using SQL’s arithmetic operators.

-- sample syntax for temperature conversion formula
SELECT temp, round(5 * (temp_reading - 32) / 9, 2) as Celsius FROM Temp_Data WHERE quant = 'temp';

Additional Resources#

Contents