How to Filter Records with Aggregate Function AVG

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.

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:

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.