Re: SQL select vs group by

I'd say there is no error here, as you've seen the same behavior in other RDBMS'. It IS reporting everything based on the GROUP BY, so you do have 1 CDTLMT of 200, 2 of 400, 3 of 700, all have different BALDUE for each instance. Your question is a good one - I don't think I've seen a reason for doing something like this, as it breaks down the results into pretty-much useless information, seems to me.

Cheers
Vern

On 6/22/2022 3:00 PM, smith5646midrange@xxxxxxxxx wrote:

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