Back to cookbooks list Articles Cookbook

How to Filter Records with Aggregate Function AVG


You want to filter groups of rows in by the average value of a given column.


Our database has a table named product with data in the following columns: id, name, store 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 products for which the average price is higher than 3.00.


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

Here’s the result:



In this query, you calculate the average price of each product. Because you calculate the value for each group of rows (we group rows by product name), the query has a GROUP BY clause with the name of the column to group rows (GROUP BY name). To calculate the average value for each group of rows, we use the aggregate AVG function, and give it the column price 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 AVG(price) > 3.00: we verify the average price is higher than 3.00.

The query displayed only one product, bread, with an average price higher than three.

Recommended courses:

Recommended articles:

See also: