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 ?
You cannot use aggregate functions in a WHERE clause or in a JOIN condition. However, a SELECT statement with aggregate functions in its SELECT list often includes a WHERE clause that restricts the rows to which the aggregate is applied.
Aggregate functions are not allowed because the WHERE clause is used for filtering data before aggregation. So while WHERE isn't for aggregation, it has other uses. To filter data based on an aggregate function result, you must use the HAVING clause.
Aggregate functions are used to perform operations across entire columns (which could include millions of rows of data or more).
Aggregate functions can appear in select lists and in ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides the rows of a queried table or view into groups.
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
try it with the "having" clause!
SELECT a,
SUM(b) FROM foo
GROUP BY a having sum(b) > 9;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With