Обсуждение: 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