How to Filter Records with Aggregate Function AVG

Database:

Operators:

AVG
HAVING

Table of Contents

Problem
Example
Solution
Discussion

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.

id	name	store	price
1	milk	Green Shop	2.34
2	bread	Clark’s Grocery	3.56
3	bread	Super Market	4.15
4	milk	Super Market	1.80
5	bread	Grocery Amanda	2.26
6	milk	Violet Grocery	3.45
7	milk	Clark’s Grocery	2.10
8	bread	Violet Grocery	2.55
9	milk	Grocery Amanda	1.95

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:

name	avg
bread	3.13

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.

Problem

Example

Solution

Discussion

Recommended courses:

Recommended articles:

See also: