Using GROUP BY with a Compound Query

[alkfhk]

Anyone knows how to put a GROUP BY under a compound query? It means that 2 queries joined by UNION and GROUP BY at the end of the 2 queries instead of GROUP BY each of them individually. Pls refer to the following example!

Select
COL1,
COL2,
From
TABLE1,
TABLE2
WHERE
...
UNION
Select
COL3,
COL4,
From
TABLE3,
TABLE4
Where
...

GROUP BY
...

 

[skrandel]

This is not possible in SQL.

However, if you have the indivdual Group By's BO will handle it automatically at the report level.

Steve Krandel
Westbay Solutions

http://www.westbaysolutions.com

 

[blom0344]

Your example is incorrect itself. A group by clause is only meaningfull when aggregates are involved, like:

Select COL1,SUM(COL2) from TABLE1,TABLE2 WHERE...........
GROUP BY COL1
union
Select COL3,SUM(COL4) from TABLE3,TABLE4 WHERE...........
GROUP BY COL3

What you want can be achieved by storing the UNION structure in a view and then creating your objects on the view. An other option is to write a temp set directly with SQL like:


Select A,sum(B) FROM
(Select COL1 as A,COL2 AS B FROM ..... WHERE.....
UNION
..............................................) TEMP
GROUP BY A


T. Blom
Information analyst
tbl@shimano-eu.com