Using an aggregate function in where

Question

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

Consider the following table and exapmle 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 ?

Answer 1

try it with the "having" clause!

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

Answer 2

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