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

Author | Cornie van Schoor |

Post date | 2012-07-02T18:07:54Z |

>I have 2 (and more) tables with identical structure:by CODE

>

>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

>Hi Cornie!

>Item1, 140

>Item2, 200

>

>Is there a way I can do it with a UNION? Or is there a better way?

>

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