Re: SQL select vs group by

[Vern Hamberg <vhamberg@xxxxxxxxxxxxxxx>]

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