I understand that. It was a typo that caused me to find this.
So, ignoring my typo and looking at the example, I can group by fields that are not in my select. It seems like it should give an error so I was asking if there was a valid reason why someone would ever do it.
SELECT CDTLMT, COUNT(*)
FROM QIWS/QCUSTCDT
GROUP BY CDTLMT, BALDUE
ORDER BY CDTLMT
This is the result set.
CDTLMT COUNT ( * )
200 1
400 1
400 1
700 1
700 1
700 1
1,000 1
5,000 1
5,000 1
9,999 1
9,999 1
9,999 1
-----Original Message-----
From: Juan Concepcion <Juan.Concepcion@xxxxxxxxxxxxx>
Sent: Wednesday, June 22, 2022 8:39 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Cc: smith5646midrange@xxxxxxxxx
Subject: RE: SQL select vs group by
If you use no comma between CDTLMT and BALDUE as follow:
SELECT CDTLMT BALDUE, COUNT(*)
FROM QIWS/QCUSTCDT
GROUP BY CDTLMT, BALDUE
ORDER BY CDTLMT
Then BALDUE will be considered an Alias of CDTLMT.
Juan Concepcion | CU*Answers | Applications Programmer
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of smith5646midrange@xxxxxxxxx
Sent: Thursday, June 16, 2022 6:07 PM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL select vs group by
WARNING: EXTERNAL EMAIL. Do NOT CLICK on links or open attachments unless you are expecting it or have verified with the sender.
Someone asked me to post a real example so I am using QCUSTCDT so everyone can look at it.
This is a clean example.
SELECT CDTLMT, COUNT(*)
FROM QIWS/QCUSTCDT
GROUP BY CDTLMT, BALDUE
ORDER BY CDTLMT
This is the result set.
CDTLMT COUNT ( * )
200 1
400 1
400 1
700 1
700 1
700 1
1,000 1
5,000 1
5,000 1
9,999 1
9,999 1
9,999 1
How I found this would be an SQL like this. Note that there is not a comma between CDTLMT and BALDUE in the SELECT line but there should have been.
SELECT CDTLMT BALDUE, COUNT(*)
FROM QIWS/QCUSTCDT
GROUP BY CDTLMT, BALDUE
ORDER BY CDTLMT
This works the same way in MYSQL so obviously it is not an IBM SQL bug.
From: smith5646midrange@xxxxxxxxx <smith5646midrange@xxxxxxxxx>
Sent: Thursday, June 16, 2022 11:57 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL select vs group by
Apologies everyone, I had that backward.
Select field1, count(*)
From file 1
Group by field1, field2
You get field1 listed multiple times with different counts.
From: smith5646midrange@xxxxxxxxx <mailto:smith5646midrange@xxxxxxxxx> <smith5646midrange@xxxxxxxxx <mailto:smith5646midrange@xxxxxxxxx> >
Sent: Thursday, June 16, 2022 8:25 AM
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxxxxxxxx <mailto:midrange-l@xxxxxxxxxxxxxxxxxx> >
Subject: SQL select vs group by
I found something interesting the other day. You can have fields in your group by clause that are not in the select clause.
Select field1, count(*)
From file1
Group by field1, field2
This gives a really weird looking results set and so I and was wondering if anyone could give me a reason that you would do something like this.
Full disclosure…how I found this was that I missed a comma in my select statement. 😊
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
