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

SQL query not working when GROUP BY / HAVING is used

From
juerg.rietmann@pup.ch
Date:
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

I get the following error in the pgadmin.log file.

19.12.00 10:53:34   Executing: 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
19.12.00 10:53:34   Executing SQL Query...
19.12.00 10:53:34   Done - 0,01 Secs.
**********************************************************************
* Error - 19.12.00 10:53:34
**********************************************************************

Software
********
Program: pgAdmin
Version: 7.0.4
Sub or Function: frmSQL, cmdExecute_Click

Error Details
*************
Error No: -2147217887
Error Description: Der ODBC-Treiber unterstützt die angeforderten
Eigenschaften nicht.
Error Source: Microsoft OLE DB Provider for ODBC Drivers
DLL Error Code: 0

Memory Details
**************
Total Physical: 132435968
Total Swap: 434098176
Total Virtual: 2147352576
Available Physical: 34004992
Available Swap: 291512320
Available Virtual: 2079350784
Percentage Free: 0

System Details
**************
Processor: 586
OEM ID: 0
No. Processors: 1
Page Size: 4096

OS Details
**********
Platform: Windows NT
Version: 4.0
Build: 1381
OS Info: Service Pack 5

Environment Details
*******************
Datasource: pgmondadori
Tracking: False
TrackVer: 0
Connect: Provider=MSDASQL.1;Extended Properties
="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD

******;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS

"
Version: 2.6


Using the same statement without the GROUP BY and HAVING it is ok !

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'
ORDER BY Zylinder_Typen.Z_durch_soll desc

Whats wrong, according to the docs, the syntax is ok and it should be
possible to use these keywords!

============================================
PFISTER + PARTNER, SYSTEM - ENGINEERING AG
Juerg Rietmann
Grundstrasse 22a
6343 Rotkreuz
Switzerland

phone: +4141 790 4040
fax: +4141 790 2545
mobile: +4179 211 0315
============================================



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

From
Stephan Szabo
Date:
On Tue, 19 Dec 2000 juerg.rietmann@pup.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.




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

From
Jie Liang
Date:
I hope it may help:
1. if you use group clause in a select stmt, the select list must be
agregate function such as sum(field),count(field), max(field)...,
cannot use field.
2. for field have NULL field, should use field IS NULL, = NULL will give
you wrong result!


Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Tue, 19 Dec 2000 juerg.rietmann@pup.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
> 
> I get the following error in the pgadmin.log file.
> 
> 19.12.00 10:53:34   Executing: 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
> 19.12.00 10:53:34   Executing SQL Query...
> 19.12.00 10:53:34   Done - 0,01 Secs.
> **********************************************************************
> * Error - 19.12.00 10:53:34
> **********************************************************************
> 
> Software
> ********
> Program: pgAdmin
> Version: 7.0.4
> Sub or Function: frmSQL, cmdExecute_Click
> 
> Error Details
> *************
> Error No: -2147217887
> Error Description: Der ODBC-Treiber unterst�tzt die angeforderten
> Eigenschaften nicht.
> Error Source: Microsoft OLE DB Provider for ODBC Drivers
> DLL Error Code: 0
> 
> Memory Details
> **************
> Total Physical: 132435968
> Total Swap: 434098176
> Total Virtual: 2147352576
> Available Physical: 34004992
> Available Swap: 291512320
> Available Virtual: 2079350784
> Percentage Free: 0
> 
> System Details
> **************
> Processor: 586
> OEM ID: 0
> No. Processors: 1
> Page Size: 4096
> 
> OS Details
> **********
> Platform: Windows NT
> Version: 4.0
> Build: 1381
> OS Info: Service Pack 5
> 
> Environment Details
> *******************
> Datasource: pgmondadori
> Tracking: False
> TrackVer: 0
> Connect: Provider=MSDASQL.1;Extended Properties
> ="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD
> 
> ******;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS
> 
> "
> Version: 2.6
> 
> 
> Using the same statement without the GROUP BY and HAVING it is ok !
> 
> 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'
> ORDER BY Zylinder_Typen.Z_durch_soll desc
> 
> Whats wrong, according to the docs, the syntax is ok and it should be
> possible to use these keywords!
> 
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
> 
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================
> 



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

From
Jie Liang
Date:
Hope it helps:

1. If you use GROUP, the select list should sum|count|max ..., no single  field.
2. If you use NULL, the condition should be field IS [NOT] NULL, = NULL  will give the wrong answer. 

Jie LIANG

Internet Products Inc.

10350 Science Center Drive
Suite 100, San Diego, CA 92121
Office:(858)320-4873

jliang@ipinc.com
www.ipinc.com

On Tue, 19 Dec 2000 juerg.rietmann@pup.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
> 
> I get the following error in the pgadmin.log file.
> 
> 19.12.00 10:53:34   Executing: 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
> 19.12.00 10:53:34   Executing SQL Query...
> 19.12.00 10:53:34   Done - 0,01 Secs.
> **********************************************************************
> * Error - 19.12.00 10:53:34
> **********************************************************************
> 
> Software
> ********
> Program: pgAdmin
> Version: 7.0.4
> Sub or Function: frmSQL, cmdExecute_Click
> 
> Error Details
> *************
> Error No: -2147217887
> Error Description: Der ODBC-Treiber unterst�tzt die angeforderten
> Eigenschaften nicht.
> Error Source: Microsoft OLE DB Provider for ODBC Drivers
> DLL Error Code: 0
> 
> Memory Details
> **************
> Total Physical: 132435968
> Total Swap: 434098176
> Total Virtual: 2147352576
> Available Physical: 34004992
> Available Swap: 291512320
> Available Virtual: 2079350784
> Percentage Free: 0
> 
> System Details
> **************
> Processor: 586
> OEM ID: 0
> No. Processors: 1
> Page Size: 4096
> 
> OS Details
> **********
> Platform: Windows NT
> Version: 4.0
> Build: 1381
> OS Info: Service Pack 5
> 
> Environment Details
> *******************
> Datasource: pgmondadori
> Tracking: False
> TrackVer: 0
> Connect: Provider=MSDASQL.1;Extended Properties
> ="DSN=pgmondadori;DATABASE=mondadori;SERVER=srvlnx01.pup.ch;PORT=5432;UID=postgres;PWD
> 
> ******;READONLY=0;PROTOCOL=6.4;FAKEOIDINDEX=1;SHOWOIDCOLUMN=1;ROWVERSIONING=0;SHOWSYSTEMTABLES=0;CONNSETTINGS
> 
> "
> Version: 2.6
> 
> 
> Using the same statement without the GROUP BY and HAVING it is ok !
> 
> 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'
> ORDER BY Zylinder_Typen.Z_durch_soll desc
> 
> Whats wrong, according to the docs, the syntax is ok and it should be
> possible to use these keywords!
> 
> ============================================
> PFISTER + PARTNER, SYSTEM - ENGINEERING AG
> Juerg Rietmann
> Grundstrasse 22a
> 6343 Rotkreuz
> Switzerland
> 
> phone: +4141 790 4040
> fax: +4141 790 2545
> mobile: +4179 211 0315
> ============================================
>