[FAQ] trouble with this select GROUP BY, fields must be in GROUP BY

[Ruslan Zasukhin]

On 31/10/07 3:49 AM, "Joseph Morgan" <joseph at checkos.com> wrote:

Hi Joseph

> Hello everyone,
> 
> Ok here is my select:
> 
> SELECT departmentName as 'myDepartmentName',sum
> (itemsales.itemsalesamount) as 'itemsalesAMT'
> FROM departments,itemsales where departments._rowID =
> itemsales.itemsalesdepartmentID
> group by itemsalesdepartmentID
> order by itemsalesAMT Desc
> 
> and of course I get this error:
> "Kernel error: 0x42508. In the SELECT clause you can use only fields
> listed in the
> GROUP BY and aggregative functions."

Absolutely correct.
 
> I see that it is illegal to mix aggregate and non aggregates but in
> mysql this select works.

Yes it works in mySQL.

And they warn by BIG BIG letters be careful with this feature.


> Any ideas on how to cheat and work around this?
> 
>   i want this result:
> myDepartmentName  itemsalesAMT
> ----------------------------  --------------------
> dept1     100.00
> dept2     104.00
> dept3     50.00

So here is GROUPING by myDepartmentName, right?

Then WHY in your mySQL query you
make group by itemsalesdepartmentID ???


> One more thing,
> For me the changes required to switch from mysql to valentina have
> been somewhat difficult.

Because mySQL have a lots of DIRTY solutions,
    which are danger and which are not from SQL Standard.

If you use them (suddenly or with intent) then you get issues.
Use SQL Standard and you will be more portable.

> Am I the only one? It would be encouraging to know that I am not the
> only one struggling.

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