RE: SQL select vs group by

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. 😊