RE: SQL select vs group by

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