[comp.databases] mix of set operation and group by in SQL

poc@cathedral.cerc.wvu.wvnet.edu (Pedro Oscar Cubillos) (08/24/90)

	Can you GROUP BY a query that is a UNION (or other set operation)?
I would like to know the standard and available commercial products.

	Thanks	

kent@manzi.unx.sas.com (Paul Kent) (08/28/90)

In article <711@babcock.cerc.wvu.wvnet.edu> poc@cathedral.cerc.wvu.wvnet.edu (Pedro Oscar Cubillos) writes:
>
>	Can you GROUP BY a query that is a UNION (or other set operation)?
>I would like to know the standard and available commercial products.
>

The Current SQL standard does not permit this.
It is permitted by the proposed SQL2 standard. Its been a while since i attended
the committee meetings (X3H2) and have no idea how close we are to a public
review, tho.


The spirit of SQL would have it that this is allowed. After all, a closed
system like SQL produces and consumes tables. a UNION produces a table
from two/more underlying tables, and a GROUP BY partitions that table 
into groups (usually for the purpose of computing some aggregate for each
group)



SAS's PROC SQL allows this...


  select group, sum(amount) as total
    from ( select group, amount
             from tablea
           union
           select group, amount
             from tableb
         )
   group by group;


Some systems may allow you to code the union up as a view, and select
the statistic from that view. SQL2 permits you to code the "view definition"
in-line on the from clause.

Oh yes, its not very portable to name your columns with "reserved" words.
And SQL2 introduces a slew of new ones....  do you have a column named CASE?

--
Paul Kent [kent@unx.sas.com]          " nothing ventured, nothing disclaimed "
SAS Institute Inc, SAS Campus Dr, Cary NC 27513-2414.