Back to list Standard SQL How to Filter Records with Aggregate Function SUM Database: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Operators:SUM, HAVING Problem: You need to find rows in which groups have a sum of values in one column less than a given value. 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: To filter records using the aggregate function (the function SUM from earlier), use the HAVING clause. To calculate the sum of values for each group of rows, use the aggregation SUM function. In this query, the column department is calculated with the sum of all salaries of its employees as a group (calculate it with the SUM function with the column salary as an argument). Because you calculate the value for each group of rows (we group rows according to the name of the department), the query has a GROUP BY clause with the name of the column to group rows (in our example, GROUP BY department). The last step is using the aggregate function in 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. From above, it's the salary sums with a value less than 7000 (SUM(salary)<7000). Here, 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 Basics in SQL Server SQL Practice Set Recommended articles: An Introduction to Using SQL Aggregate Functions with JOINs A Beginner’s Guide to SQL Aggregate Functions See also: How to Filter Records with Aggregate Function COUNT How to Filter Records with Aggregate Function AVG Tags: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.