Thread: 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