Back to list Standard SQL How to Filter Records with Aggregate Function AVG Database: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Operators:AVG, HAVING Problem: You want to find groups of rows in which the average of values in a column is higher or lower than a given value. Example: Our database has a table named product with data in the following columns: id, name, grocery and price. idnamegroceryprice 1milkGreen Shop2.34 2breadClark’s Grocery3.56 3breadSuper Market4.15 4milkSuper Market1.80 5breadGrocery Amanda2.26 6milkViolet Grocery3.45 7milkClark’s Grocery2.10 8breadViolet Grocery2.55 9milkGrocery Amanda1.95 Let’s find the names of products in which the average price of each product among groceries is higher than 3.00. Solution: SELECT name, AVG(price) FROM product GROUP BY name HAVING AVG(price)>3.00; Here’s the result: nameavg bread3.13 Discussion: To filter records using the aggregate function, use the HAVING clause. Here we calculate the aggregate value: the average price of each product. One is sold by more than one grocer; therefore the average price is calculated for each (in our example, SELECT name, AVG(price)). Beside the aggregate function, we also use the column name in SELECT, so we should use GROUP BY with this column name (GROUP BY name). The last step is using the aggregate function in the HAVING clause. Remember that HAVING should be put after the GROUP BY clause. It contains the condition which compares the value returned by the aggregate function with a given value. Above, it’s the average price of the product with a value 3.00 (HAVING AVG(price)>3.00). In this query, we check if the average price each product in all groceries is higher than three. The query displayed only one product, bread, with an average price higher than three. 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 How to Filter Records with Aggregate Function SUM Tags: SQL PostgreSQL MS SQL Server Oracle MySQL SQLite Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.