PostgreSQL: aggregate expression on a subquery

Question

A total beginner's question: I wanted to run a sub-query with GROUP BY statement, and then find out a row with maximum value in the result. I have built an expression like that below:

SELECT agg.facid, agg.Slots
FROM
(SELECT facid AS facid, SUM(slots) AS Slots FROM cd.bookings
GROUP BY facid
ORDER BY SUM(slots) DESC) AS agg
WHERE agg.Slots = (SELECT MAX(Slots) FROM agg);

In my mind, this should first create a 2-column table with facid and SUM(slots) values, and then by addressing these columns as agg.facid and agg.Slots I should get only the row with max value in "Slots". However, instead I am getting this error:

ERROR:  relation "agg" does not exist
LINE 6: WHERE agg.Slots = (SELECT MAX(Slots) FROM agg);

This is probably something very simple, so I am sorry in advance for a silly problem ;)

I am working on PostgreSQL 10, with pgAdmin 4.

Check out the HAVING clause, should be able to do it without a subquery even. — R Scott
– R Scott, Commented Aug 1, 2018 at 1:42
If I using HAVING statement like so, I still get an error:SELECT agg.facid, agg.Slots FROM (SELECT facid AS facid, SUM(slots) AS Slots FROM cd.bookings GROUP BY facid HAVING Slots = MAX(Slots)) AS agg; ERROR: column "bookings.slots" must appear in the GROUP BY clause or be used in an aggregate function — Greem666
– Greem666, Commented Aug 1, 2018 at 1:48

klin · Accepted Answer · 2018-08-01 02:02:53Z

4

Use a Common Table Expression:

WITH agg AS (
    SELECT facid AS facid, SUM(slots) AS Slots 
    FROM cd.bookings
    GROUP BY facid
    )
SELECT agg.facid, agg.Slots
FROM agg
WHERE agg.Slots = (SELECT MAX(Slots) FROM agg);

edited Aug 1, 2018 at 2:02

answered Aug 1, 2018 at 1:57

klin

123k15 gold badges240 silver badges262 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Greem666 Over a year ago

Ah, noticed your answer after the web page refreshed upon posting an answer to my own question ;) Thanks for your feedback mate!

Greem666 · Accepted Answer · 2018-08-01 04:46:49Z

1

So a bit more of a research, and I figured a solution which might be clean enough to my liking, using a Common Table Expression:

WITH sum AS (SELECT facid, SUM(slots) AS Slots FROM cd.bookings GROUP BY facid)
SELECT facid, Slots
FROM sum
WHERE Slots = (SELECT MAX(Slots) FROM sum);

The first line declares a CTE, which can later be called for a sub-query calculating what is the max value in aggregated slots column. Hope it helps anyone interested.

answered Aug 1, 2018 at 4:46

Greem666

9491 gold badge14 silver badges28 bronze badges

Comments

R Scott · Accepted Answer · 2018-08-01 01:54:03Z

0

Does this do what you are looking for?

SELECT
    facid,
    SUM(slots)
FROM cd.bookings
GROUP BY
    facid
HAVING SUM(slots) = MAX(slots)

answered Aug 1, 2018 at 1:54

R Scott

1762 silver badges5 bronze badges

1 Comment

Greem666 Over a year ago

Unfortunately, it does not work mate, because that MAX(slots) function just picks a max value in a single slots cell, before summing them up by facid.

Collectives™ on Stack Overflow

PostgreSQL: aggregate expression on a subquery

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related