Like most other relational database products, PostgreSQL supports aggregate functions.

An aggregate function computes a single result from multiple input rows. For example, there are aggregates to compute the count, sum, avg (average), max (maximum) and min (minimum) over a set of rows.

For example if you want to find out the product with highest unit price then we can query like this:

I am using the database available in my Github public repo: https://github.com/ydchauh/yogeshchauhan.com-public


SELECT MAX(unit_price) FROM products;

//Output

Max
263.5

What if we want to find out the name of the product with that price as well:


SELECT product_name FROM products WHERE unit_price = MAX(unit_price); //THIS IS WRONG

This will not work since the aggregate max cannot be used in the WHERE clause.

This restriction exists because the WHERE clause determines the rows that will go into the aggregation stage; so it has to be evaluated before aggregate functions are computed.

However, as is often the case the query can be restated to accomplish the intended result, here by using a subquery:


SELECT product_name FROM products
    WHERE unit_price = (SELECT MAX(unit_price) FROM products);

//Output
product_name
"Côte de Blaye"

This is OK because the subquery is an independent computation that computes its own aggregate separately from what is happening in the outer query.

We saw GROUP BY clause examples in this post: Order By And Group By In Postgres

Aggregates are also very useful in combination with GROUP BY clauses.

For example, we can get the maximum unit_price observed in each category_id with:


SELECT category_id, MAX(unit_price)
    FROM products
    GROUP BY category_id;

//Output
category_id, MAX
8	62.5
7	53
1	263.5
5	38
.....
.....

Of course the results are unordered as I haven't specified ORDER BY clause.

You can learn about ORDER BY clause in the same blog post: Order By And Group By In Postgres

Each aggregate result is computed over the table rows matching that category_id.

We can filter these grouped rows using HAVING:


SELECT category_id, MAX(unit_price)
    FROM products
    GROUP BY category_id
    HAVING max(unit_price) < 40;

//Output
category_id, MAX
5	38

We can get the category_name using JOIN as category details are in the different table but that's not the point of discussion as per now.

We can also use WHERE and HAVING together:

Let's say we just care about only the product whose names start with 'Ma'. So, we can apply query like this:


SELECT product_name, category_id, MAX(unit_price)
    FROM products
	WHERE product_name LIKE 'Ma%'
    GROUP BY category_id, product_name
    HAVING max(unit_price) < 40;

//Output
product_name, category_id, MAX
"Maxilaku"	3	20
"Mascarpone Fabioli"	4	32

Now, of course when you want product_name to be displayed then you must specify it in the GROUP BY clause if you're not using it in aggregate functions.

It is important to understand the interaction between aggregates and SQL's WHERE and HAVING clauses.

The fundamental difference between WHERE and HAVING is this:

WHERE selects input rows before groups and aggregates are computed (thus, it controls which rows go into the aggregate computation), whereas HAVING selects group rows after groups and aggregates are computed.

Thus, the WHERE clause must not contain aggregate functions; it makes no sense to try to use an aggregate to determine which rows will be inputs to the aggregates.

On the other hand, the HAVING clause always contains aggregate functions.

Strictly speaking, you are allowed to write a HAVING clause that doesn't use aggregates, but it's seldom useful. The same condition could be used more efficiently at the WHERE stage.

Sources:

  • https://www.postgresql.org/docs/8.1/tutorial-agg.html

8 Comments

Pompeo

Apr 18, 2020 12:04:43 pm

I think that is one of the such a lot significant info for me. And i'm glad studying your article. However wanna remark on few general issues, The web site style is ideal, the articles is in reality great : D. Just right activity, cheers

alex

Apr 16, 2020 01:04:46 pm

Vеry good info. Luky me І recеntly found your site by accident. Good Job!

Henry C

Apr 13, 2020 04:04:23 am

Peculiar article, just what I was looking for.

Kaema

Apr 11, 2020 01:04:36 pm

I'm not sure why but this blog is loading incredibly slow for me. Is anyone else having this problem or is it a issue on my end? I'll check back later on and see if the problem still exists.

Daniel

Apr 11, 2020 05:04:16 am

Way cool! Some very valid points! I appreciate you writing this article and also the rest of the site is also really good.

Philip

Apr 10, 2020 10:04:46 am

It's appropriate time to make some plans for the future and it's time to be happy. I have read this post and if I could I wish to suggest you some interesting things or tips. Maybe you can write next articles referring to this article. I want to read even more things about it!

Kuku

Apr 08, 2020 03:04:11 pm

Thank you, I've just been looking for information approximately this topic for ages and yours is the best I've came upon so far. However, what about the conclusion? Are you certain concerning the supply?

Zaraa

Apr 08, 2020 02:04:12 am

Heya are using Wordpress for your site platform? I'm new to the blog world but I'm trying to get started and set up my own. Do you require any html coding knowledge to make your own blog? Any help would be really appreciated!

Leave a reply

Your email address will not be published. required fields are marked *