RE: SQL Group by Question, WHY?

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
--
[ Picked text/plain from multipart/alternative ]
Truth be known, this is what happened... And it's all Microsoft's fault :-)

I was asked to take an Access database program, 10 tables, 70 queries, ton
of reports, and place all the data on the as/400, but still allow the forms
and reports in access to be the entry and the report mechanism, but during
the process, I kept getting ODBC calls failing because I had reports,
grouping, but the query, wasn't adding the group field, into the SQL
statement, so after reviewing the ODBC trace file, I finally found the issue
in the way access can do a group by, without having to issue the proper SQL
statement...  So I wasn't sure if everything I had to do to make this work,
would have been needed if I was on a different version of Os/400  .

That's all...  But I ported it, and it seems to work fine... They just
wanted the ability to allow the 400 to join this access data with data on
our 400, and then be web served from that point.

Tim


> -----Original Message-----
> From: Vernon Hamberg [SMTP:vhamberg@attbi.com]
> Sent: Thursday, June 06, 2002 7:53 PM
> To:   midrange-l@midrange.com
> Subject:      Re: SQL Group by Question, WHY?
>
> Basically, grouping is done after record selection. The result set after
> selection needs to have the group by fields in it, hence they need to be
> in
> the select list.
>
> It's not clear what you want to do. You can't just have fld2 and fld3
> either. If you group by fld1, there have to be aggregate functions
> performed on the other 2 fields, like max(fld2) or avg(fld3). The result
> is, say, the maximum of the values for fld2 for each unique value of fld1.
>
> AFAIK, this is standard behavior for all flavors of SQL.
>
> At 10:49 AM 6/6/02 -0700, you wrote:
> >I'm running version 4.5, and I was wondering if all versions of SQL
> require
> >this...
> >
> >if I have a table,
> >
> >Timstable
> >------------------
> >fld1
> >fld2
> >fld3
> >
> >and I want to group this table, but I only need fld2 and fld3, but I want
> to
> >group by fld1,  why do I have to put fld1 in the select portion, isn't
> group
> >by enough?
> >
> >Thanks, tim
>
>
> Vern Hamberg
>
> Would you like to see a challenging little arithmetic puzzle
> that might get you or your kids or grandkids more interested
> in math? Go to <http://cgi.wff-n-proof.com/MSQ-Ind/I-1E.htm>
>
> Sillygism--
>
> Something is better than nothing.
> Nothing is better than a ham sandwich.
> Ergo
> Something is better than a ham sandwich.
>
> _______________________________________________
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
> list
> To post a message email: MIDRANGE-L@midrange.com
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
> or email: MIDRANGE-L-request@midrange.com
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
This e-mail message, including any attachments, is for the sole use of the
intended recipient(s) and may contain confidential or privileged
information.  Any unauthorized review; use, disclosure or distribution is
prohibited.  If you are not the intended recipient, please contact the
sender by reply e-mail and destroy the message.