Using an aggregate function in where

Question

I saw a few threads here with similar questions however I couldn't find something fitting my needs.

Consider the following table and example data:

CREATE TABLE foo(a int, b int);

INSERT INTO FOO VALUES(1, 3);
INSERT INTO FOO VALUES(1, 3);
INSERT INTO FOO VALUES(1, 4);
INSERT INTO FOO VALUES(2, 5);
INSERT INTO FOO VALUES(2, 3);
INSERT INTO FOO VALUES(3, 10);

Consider this query:

SELECT a,
       sum(b)
FROM foo
GROUP BY a;

It works fine. I want to alter that query so that it will only match groups where the sum is bigger than 9. My (failed) attempt is:

SELECT a,
       SUM(b)
FROM foo
WHERE SUM(b) >9
GROUP BY a;

What is the correct way to do it in postgres ?

possible duplicate of Aggregate function in SQL WHERE-Clause — GolezTrol
– GolezTrol, Commented Jul 28, 2014 at 14:45
In case you wondered, I downvoted this question. To prove this question is asked a lot, I pasted the exact title of the question in Google. The first two results were exact duplicates of this question on StackOverflow. Therefor, I think this question, though coherent and nicely formatted, doesn't show much research effort. — GolezTrol
– GolezTrol, Commented Jul 28, 2014 at 14:47

Mureinik · Accepted Answer · 2014-07-28 14:44:21Z

12

You can't use aggregate expression in the where clause - this is what the having clause is for:

SELECT   a, SUM(b)
FROM     foo
GROUP BY a
HAVING   SUM(b) > 9

answered Jul 28, 2014 at 14:44

Mureinik

316k54 gold badges400 silver badges405 bronze badges

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

Comments

deru · Accepted Answer · 2014-07-28 14:46:12Z

1

try it with the "having" clause!

SELECT a,
   SUM(b) FROM foo
GROUP BY a having sum(b) > 9;

answered Jul 28, 2014 at 14:46

deru

4701 gold badge5 silver badges16 bronze badges

Collectives™ on Stack Overflow

Using an aggregate function in where

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related