Back to cookbooks list Articles Cookbook

How to Filter Records with Aggregate Function AVG


You want to find groups of rows in which the average of values in a column is higher or lower than a given value.


Our database has a table named product with data in the following columns: id, name, grocery and price.

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.

Download SQL Basics Cheat Sheet


SELECT name, AVG(price)
FROM product
HAVING AVG(price)>3.00;

Here’s the result:



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:

Recommended articles:

See also: