Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using an aggregate function in where

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 ?

like image 515
Stanimirovv Avatar asked Jul 28 '14 14:07

Stanimirovv


People also ask

Can we use aggregate function in WHERE in SQL?

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.

Can we use aggregate function to filter values in WHERE clause?

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.

When would you use an aggregate function?

Aggregate functions are used to perform operations across entire columns (which could include millions of rows of data or more).

Can we use aggregate function in WHERE clause in Oracle?

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.


2 Answers

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
like image 133
Mureinik Avatar answered Sep 21 '22 07:09

Mureinik


try it with the "having" clause!

SELECT a,
   SUM(b) FROM foo
GROUP BY a having sum(b) > 9;
like image 25
deru Avatar answered Sep 20 '22 07:09

deru