How to Filter Records with Aggregate Function SUM Database: Standard SQL PostgreSQL MS SQL Server Oracle MySQL SQLite 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. iddepartmentfirst_namelast_namesalary 1marketingLoraBrown2300 2financeJohnJackson3200 3marketingMichaelThomson1270 4productionTonyMiller6500 5productionSallyGreen2500 6financeOlivierBlack3450 7productionJenifferMichelin2800 8marketingJeremyLorson3600 9marketingLouisSmith4200 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: departmentsum finance6550 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. Recommended courses: SQL Basics SQL Practice Set Standard SQL Functions Recommended articles: SQL Basics Cheat Sheet The SQL HAVING Clause Explained HAVING vs. WHERE in SQL: What You Should Know SQL SUM() Function Explained with 5 Practical Examples How to Use SUM() with GROUP BY: A Guide with 8 Examples 10 GROUP BY SQL Practice Exercises with Solutions SQL Aggregate Functions Cheat Sheet GROUP BY and Aggregate Functions: A Complete Overview Top 9 SQL GROUP BY Interview Questions See also: How to Filter Records with Aggregate Function COUNT How to Filter Records with Aggregate Function AVG Subscribe to our newsletter Join our monthly newsletter to be notified about the latest posts. Email address How Do You Write a SELECT Statement in SQL? What Is a Foreign Key in SQL? Enumerate and Explain All the Basic Elements of an SQL Query