CODEWITHSUNDEEP
sqlms-access
Orang123
Orang123

Reputation: 13

sql sum data from multiple tables with entries from both tables

I have 2 tables tbl1 and tbl2 where both have the columns [PartID] and [Amount] and [Date].

I want a query to return something like this :

PartID | Date | SUM_tbl1 | SUM_tbl2

PartID 1 maybe in tbl1 but not in tbl2 and vice versa and each PART ID has multiple rows in both tables.

I want both SUM columns to show SUM of each Part IDs in that table. And I want to see SUM as of 6/30/2017.

Please help! and thanks!

Upvotes: 0

Views: 171

Answers (1)

Kevin
Kevin

Reputation: 2243

The simplest? First, use a UNION to assemble a single table with the raw rows you'd need:

select PartID, Date, Amt as Table1Entry, NULL as Table2Entry from tbl1
UNION ALL
select PartID, Date, NULL as Table1Entry, Amt as Table2Entry from tbl2

... next, now that you've got both tables's data together, just sum from that as a subquery:

select PartID, Date, Sum(Table1Entry) as SumTable1, Sum(Table2Entry) as SumTable2
from
(
    select PartID, Date, Amt as Table1Entry, NULL as Table2Entry from tbl1
    UNION ALL
    select PartID, Date, NULL as Table1Entry, Amt as Table2Entry from tbl2
) mySubquery
GROUP BY PartID, Date

Upvotes: 2

Related Questions