SQL query aggregate function

[Ruslan Zasukhin]

On 8/9/08 9:32 AM, "Thorsten Hohage" <thohage at genericobjects.de> wrote:

> Hi William,
> 
> On 2008-08-09, at 01:45, william humphrey wrote:
> 
>> 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.
> 
> 
> Valentina is a little bit "special" here - let me at "still" and hope
> that this will be changed / improved in the near future!
> 
> But the solution is pretty simple, use a inner sql query and not a
> table to select from
> 
> select day, count(*) TheCOUNT from test group by day
> 
> and now put this query in another
> 
> select day, TheCOUNT from (select day, count(*) TheCOUNT from test
> group by day) where TheCOUNT > 1;
> 
> 
> (Note this is from brain and without test - maybe typo - but you
> should get the idea)

Simpler, Thorsten :)

Just use field alias.

-- 
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]