CODEWITHSUNDEEP
sqlms-access
Heru Nugroho
Heru Nugroho

Reputation: 25

Query sum and group from 2 different table

Please help me with query at ms-access

I have to tables for ex:

Table A:
Prim_key For_key Total
1       ID1     10
2       ID1     20
3       ID2     30
4       ID2     10
Table B:
Prim_key For_key Total
1        ID1      20
2        ID1       5
3        ID2      20
4        ID2      10

I Need to make select and show For_key And Sum total field from table A and B and substract the total sum, like this

For Key Total_A Total_B Substract
ID1     30       25      5
ID2     40       30      10

Sory for my bad english. And thx for your help

Upvotes: 0

Views: 31

Answers (1)

trincot
trincot

Reputation: 350034

You could use a subquery with UNION ALL to make sure you also get ID values that only exist in one of the two tables:

SELECT   For_key, 
         SUM(TotalA) AS Total_A, 
         SUM(TotalB) AS Total_B,
         SUM(TotalA)-SUM(TotalB) AS Subtract 
FROM     (
            SELECT  For_key, Total AS TotalA, 0 AS TotalB 
            FROM A
            UNION ALL
            SELECT  For_key, 0, Total
            FROM B
         ) AS C
GROUP BY For_key

Upvotes: 1

Related Questions