How to Filter Records with Aggregate Function AVG

Problem:

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

Example:

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

Let’s find the products for which the average price is higher than 3.00.

Solution:

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

Here’s the result:

Discussion:

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.