>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
