Re: SQL query not working when GROUP BY / HAVING is used

On Tue, 19 Dec 2000 juerg(dot)rietmann(at)pup(dot)ch wrote:

> Hello there
>
> I have a question regarding a SQL statement.
>
> When I execute (and that's what I need)
>
> SELECT Zylinder.Z_durch_soll, Zylinder.Z_id, Zylinder.Z_durch_ist,
> ((Zylinder.Z_durch_soll+0.12) - Zylinder.Z_durch_ist) AS durchmesserdelta,
> (Zylinder.Z_durch_soll+0.12) AS effektiv
> FROM Auftrag,Zylinder_Typen, Zylinder
> WHERE Auftrag.A_nr = '11'
> AND ( Auftrag.A_farbe_1 > '0' AND Zylinder_Typen.Z_farbe='1' AND
> Zylinder_Typen.Z_SW='0')
> AND Zylinder_Typen.Z_durch_soll = Zylinder.Z_durch_soll
> AND Auftrag.A_Ztyp=Zylinder.Z_typ
> AND Z_A_nr = NULL
> AND Z_status = 'zcu'
> GROUP BY Zylinder.Z_durch_soll
> HAVING durchmesserdelta >= 0.085
> ORDER BY Zylinder_Typen.Z_durch_soll desc

This query is not legal SQL. All columns in the select list of
a group by query must either be grouped columns or set value
functions (pretty much anyway). The general construct is legal
but there are syntax rules for GROUP BY that you are violating.

If Z_id and Z_durch_ist are unique for values of Z_durch_soll
you might try grouping on all of them. If not, the query above
is indeterminate since you are not specifying which Z_id and
which Z_durch_ist to use for a particular Z_durc_soll value.