pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL query not working when GROUP BY / HAVING is used
4+ messages / 3 participants
[nested] [flat]
* SQL query not working when GROUP BY / HAVING is used
@ 2000-12-19 09:59 juerg.rietmann@pup.ch
2000-12-19 16:29 ` Re: SQL query not working when GROUP BY / HAVING is used Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-19 17:35 ` Re: SQL query not working when GROUP BY / HAVING is used Jie Liang <jliang@jliang.ipinc.com>
2000-12-19 17:39 ` Re: SQL query not working when GROUP BY / HAVING is used Jie Liang <jliang@jliang.ipinc.com>
0 siblings, 3 replies; 4+ messages in thread
From: juerg.rietmann@pup.ch @ 2000-12-19 09:59 UTC (permalink / raw)
To: pgsql-sql
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
============================================
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL query not working when GROUP BY / HAVING is used
2000-12-19 09:59 SQL query not working when GROUP BY / HAVING is used juerg.rietmann@pup.ch
@ 2000-12-19 16:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2 siblings, 0 replies; 4+ messages in thread
From: Stephan Szabo @ 2000-12-19 16:29 UTC (permalink / raw)
To: juerg.rietmann@pup.ch; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL query not working when GROUP BY / HAVING is used
2000-12-19 09:59 SQL query not working when GROUP BY / HAVING is used juerg.rietmann@pup.ch
@ 2000-12-19 17:35 ` Jie Liang <jliang@jliang.ipinc.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jie Liang @ 2000-12-19 17:35 UTC (permalink / raw)
To: juerg.rietmann@pup.ch; +Cc: pgsql-sql
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
> ============================================
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: SQL query not working when GROUP BY / HAVING is used
2000-12-19 09:59 SQL query not working when GROUP BY / HAVING is used juerg.rietmann@pup.ch
@ 2000-12-19 17:39 ` Jie Liang <jliang@jliang.ipinc.com>
2 siblings, 0 replies; 4+ messages in thread
From: Jie Liang @ 2000-12-19 17:39 UTC (permalink / raw)
To: juerg.rietmann@pup.ch; +Cc: pgsql-sql
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
> ============================================
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2000-12-19 17:39 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-12-19 09:59 SQL query not working when GROUP BY / HAVING is used juerg.rietmann@pup.ch
2000-12-19 16:29 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2000-12-19 17:35 ` Jie Liang <jliang@jliang.ipinc.com>
2000-12-19 17:39 ` Jie Liang <jliang@jliang.ipinc.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox