Aggregates not allowed in WHERE clause?
Hi All,
I have a table (table info) with 2 column, column 'name' and column 'quantity'.
name | quantity
--------------------------
a | 5
b | 3
a | 3
c | 4
b | 6
If I want to sum the sum of all entry in table.
name | quantity
------------------------
a | 8
b | 9
c | 4
I can use : select name, sum(quantity) from info group by name;
I want to show the sum of all entry that bigger than 5
name | quantity
------------------------
a | 8
b | 9
I try to use : select name, sum(quantity) from info where
sum(quantity)>20 group by name;
This yields the message: 'Aggregates not allowed in WHERE clause'.
Can somebody help me here....
thx,
Frans
Frans <fransgunawan@gmail.com> writes:
I try to use : select name, sum(quantity) from info where
sum(quantity)>20 group by name;
This yields the message: 'Aggregates not allowed in WHERE clause'.
Can somebody help me here....
See
http://www.postgresql.org/docs/7.4/static/tutorial-agg.html
particularly the next-to-last paragraph.
regards, tom lane
On Thu, Dec 16, 2004 at 12:02:34PM +0700, Frans wrote:
I try to use : select name, sum(quantity) from info where
sum(quantity)>20 group by name;
This yields the message: 'Aggregates not allowed in WHERE clause'.
For aggregates use HAVING, not WHERE:
SELECT name, SUM(quantity)
FROM info
GROUP BY name
HAVING SUM(quantity) > 20;
http://www.postgresql.org/docs/7.4/static/tutorial-agg.html
http://www.postgresql.org/docs/7.4/static/queries-table-expressions.html#QUERIES-GROUP
http://www.postgresql.org/docs/7.4/static/sql-select.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
A long time ago, in a galaxy far, far away, fransgunawan@gmail.com (Frans) wrote:
This yields the message: 'Aggregates not allowed in WHERE clause'.
Can somebody help me here....
What it says is right; SQL does not permit using aggregates in the
WHERE clause.
You need to look at the HAVING clause which is, more or less, the
equivalent to WHERE specifically for use with aggregates...
--
(format nil "~S@~S" "cbbrowne" "gmail.com")
http://www.ntlug.org/~cbbrowne/nonrdbms.html
"If use of the GPL filters out people who are only interested in
inventing the worst possible motives for my actions then, yes, I'm
happy to learn that it serves a useful purpose I'd never thought of."
-- Dave Pearson