>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

Set thanks for the reply, I just have one problem, the union removes
duplicates. So if both tables have
Item1, 50
the result is
Item1, 50 and not
Item1, 100 as I require.

Regards

Cornie