Path: utzoo!utgpu!jarvis.csri.toronto.edu!mailrus!uflorida!ukma!rutgers!att!akgua!sorsac!wcb
From: wcb@sorsac.UUCP (20SO940210)
Newsgroups: comp.databases
Subject: Re: Informix sql sum/group by question
Message-ID: <334@sorsac.UUCP>
Date: 23 Mar 89 23:21:21 GMT
References: <131@bsadrc.UUCP> <3462@sybase.sybase.com>
Reply-To: wcb@sorsac.UUCP (20SO940210)
Distribution: na
Organization: AT&T Southern Region, Atlanta
Lines: 105

In article <3462@sybase.sybase.com> jeffl@sybase.Sybase.COM (Jeff Lichtman) writes:
>In article <whatever> (Darrel R Carver) writes:
>> 	select sum(amt), amt from tr_dat where tr_vendor = 5;
>> 
>> Informix gives me an error saying the amt must be in a group by
>> statement....
>> 
>> My question is why?  Why should I have to have the amt in a group by
>> clause?
>> -- 
>> Darrel R Carver	            -	A clever quote goes here.  Have we got any in

>Informix does it according to the ANSI standard.  Sybase doesn't have this
>restriction, but I know why it exists, and I will try to explain.

An excellent explanation then follows, of which I have deleted all but the
following portion:

>- - -
>Suppose you have the following table, TABX:
>
>	COL1 | COL2 | COL3
>	------------------
>	  1  |   1  |  1
>	  2  |   1  |  1
>	  3  |   1  |  2
>	  4  |   2  |  1
>	  5  |   2  |  2
>- - -
>Now, what should the following produce:
>
>	select sum(COL1), COL2 from TABX
>
>There is no association between the aggregate and COL2.  In ANSI SQL,
>not only is the syntax illegal, but there is no definition for what this
>should produce if it were legal.  In Sybase, we give the answer:
>
>	     | COL2
>	------------
>	  15 |  1
>	  15 |  2
>	  15 |  3
>	  15 |  4
>	  15 |  5
>
>That is, since there is no grouping, Sybase simply duplicates the scalar
>(single-value) result for each value of COL2.  This is the only way I
>can think of to interpret this query, but I can't imagine why someone
>would want this result.  It would make more sense to do two selects:
>
>	select sum(COL1) from TABX
>	select COL1 from TABX
>
>- - -

With the above values in TABX, it seems the statement

	select sum(COL1), COL2 from TABX

would produce the result:

	     | COL2
	------------
	  15 |  1
	  15 |  1
	  15 |  1
	  15 |  2
	  15 |  2

Which implies to me that Sybase will make more than one pass through
the data, per select statement - which, of course, Informix will not do.

To try to get around this, I came up with the following:

	select col1 from tabx into temp temptab;
	insert into temptab values ("");
	insert into temptab select sum(col1) from tabx;
	select * from temptab;

Which will produce something like:

	col1

	   1
	   2
	   3
	   4
	   5

	  15

with a report of having retreived 7 rows. (In "running" this as a
".sql" file, the "retreive" statements that are produced can be
redirected to /dev/null - assuming you are running UNIX.)

If Sybase performs differently than I have indicated, I would be
interested in hearing about it.
 
-------------------------------------------------------------------------

-- 
"With friends like these, who needs hallucinations?" - Buddy Ryan in
                                                      "Night Court"

Bill Barksdale	        AT&T Network Systems            Atlanta, Ga.