How to Filter Records with Aggregate Function SUM

Database:

Operators:

SUM
HAVING

Table of Contents

Problem:
Example:
Solution:
Discussion:

Problem:

You need to filter groups of rows by the sum of a given column.

Example:

Our database has a table named company with data in the following columns: id, department, first_name, last_name, and salary.

id	department	first_name	last_name	salary
1	marketing	Lora	Brown	2300
2	finance	John	Jackson	3200
3	marketing	Michael	Thomson	1270
4	production	Tony	Miller	6500
5	production	Sally	Green	2500
6	finance	Olivier	Black	3450
7	production	Jeniffer	Michelin	2800
8	marketing	Jeremy	Lorson	3600
9	marketing	Louis	Smith	4200

Let’s find the names of departments that have sums of salaries of its employees less than 7000.

Solution:

SELECT department, SUM(salary)
FROM company
GROUP BY department
HAVING SUM(salary) < 7000;

Here’s the result:

department	sum
finance	6550

Discussion:

In this query, you calculate the sum of salaries for employees of each department. Because you calculate the value for each group of rows (we group rows by department name), the query has a GROUP BY clause with the name of the column to group rows (GROUP BY department). To calculate the sum of values for each group of rows, we use the aggregate SUM function, and give it the column salary as an argument.

To filter records using the aggregate function, we use the HAVING clause. Remember, HAVING should be put after GROUP BY clause. HAVING contains the condition comparing the value returned by the aggregate function with a given value. Here, our condition is SUM(salary) < 7000: we verify the salary sum in each department is lower than 7000.

The query displayed only one department, finance, with salaries of 6550.

Problem:

Example:

Solution:

Discussion:

Recommended courses:

Recommended articles:

See also: