CODEWITHSUNDEEP
sql-server
Akshay
Akshay

Reputation: 1472

SQL Server, show aggregate amount from multiple tables in single query

I have these tables, each can have multiple rows.

Each table has data rows like this:

ta:

1001  100.00
1001   20.10

tb:

1001   200.00
1001   32.10

tc:

1001   300.00
1001   20.10

Solution I need: single row with eid and aggregate of each table's amount column

1001   120.10     232.10   320.10

I tried this but it is showing the amount multiplied by number of tables I am using

select 
    ta.eid, 
    sum(ta.Amount) as taAmount, 
    sum(tb.Amount) as tbAmount, 
    sum(tc.Amount) as tcAmount
from 
    ta , tb, tc
where 
    ta.eid = tb.eid 
    and tb.eid = tc.eid
group by 
    ta.eid

Please help me know how it can be achieved ?

Upvotes: 0

Views: 25

Answers (1)

Squirrel
Squirrel

Reputation: 24763

make use of derived table or CTE to get the aggregate amount first

select ta.eid, ta.amount, tb.amount, tc.amount
from   (select eid, amount= sum(amount) from ta group by eid) ta
join   (select eid, amount= sum(amount) from tb group by eid) tb on ta.eid = tb.eid
join   (select eid, amount= sum(amount) from tc group by eid) tc on ta.eid = tc.eid

If the eid might not appear in all tables, you can get a list of eid and then LEFT JOIN to ta, tb, tc

select i.eid, ta.amount, tb.amount, tc.amount
from   (select eid from ta union select eid from tb union select eid from tc) i
left join (select eid, amount= sum(amount) from ta group by eid) ta on i.eid = ta.eid
left join (select eid, amount= sum(amount) from tb group by eid) tb on i.eid = tb.eid
left join (select eid, amount= sum(amount) from tc group by eid) tc on i.eid = tc.eid

Upvotes: 2

Related Questions