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.