RE: [firebird-support] How do you get a summary SUM() from a union of tables

[Svein Erling Tysvær]

>I have 2 (and more) tables with identical structure:
>
>Simplified structure and data:
>CODE VARCHAR(15)
>QUAN INTEGER
>
>Table A
>Item1, 50
>Item1, 40
>Item2, 70
>
>Table B
>Item1, 50
>Item2, 100
>Item2, 30
>
>I need to get the sum(QUAN) for all the rows in all the tables group by CODE
>
>Item1, 140
>Item2, 200
>
>Is there a way I can do it with a UNION?  Or is there a better way?
>

Hi Cornie!

This can be solved several ways, one simple solution would be:

WITH TMP AS
(SELECT CODE, QUAN
 FROM TableA
 UNION
 SELECT CODE, QUAN
 FROM TableB)

SELECT CODE, SUM(QUAN)
FROM TMP
GROUP BY CODE

HTH,
Set