SQL query aggregate function

[Ruslan Zasukhin]

On 8/9/08 2:45 AM, "william humphrey" <shoreagent at gmail.com> wrote:

> How come this SQL query doesn't work?
> select d_cargo_description, count(*)
> from data_cargo
> group by d_cargo_description
> having count(*) > 2
> 
> It is straight out of a SQL tutorial
> 
> The duplicated rows have a count greater than one. If you only want to see
> rows that are duplicated, you need to use a HAVING clause (not a WHERE
> clause), like this:
> 
> select day, count(*) from test group by day HAVING count(*) > 1;
> +------------+----------+
> | day        | count(*) |
> +------------+----------+
> | 2006-10-08 |        2 |
> 
> 
> I get error
> 
> Kernel error: 0x42510. Aggregative function cannot appear in the WHERE or
> HAVING clause.

You need do it as 

select d_cargo_description, count(*) as 'theCount'
from data_cargo
group by d_cargo_description
having theCount > 2



-- 
Best regards,

Ruslan Zasukhin
VP Engineering and New Technology
Paradigma Software, Inc

Valentina - Joining Worlds of Information
http://www.paradigmasoft.com

[I feel the need: the need for speed]