SQL Query to sum fields from different tables

Question

I'm a humble programmer that hates SQL ... :) Please help me with this query.

I have 4 tables, for example:

Table A:
Id Total
1  100
2  200
3  500

Table B
ExtId  Amount
1      10
1      20
1      13
2      12
2      43
3      43
3      22

Table C
ExtId  Amount
1      10
1      20
1      13
2      12
2      43
3      43
3      22

Table D
ExtId  Amount
1      10
1      20
1      13
2      12
2      43
3      43
3      22

I need to make a SELECT that shows the Id, the Total and the SUM of the Amount fields of tables B, C and D like this

Id Total AmountB AmountC AmountD
1  100   43      43      43
2  200   55      55      55
3  500   65      65      65

I've tried with a inner join of the three tables by the Id and doing a sum of the amount fields but results are not rigth. Here is the wrong query:

SELECT     dbo.A.Id, dbo.A.Total, SUM(dbo.B.Amount) AS Expr1, SUM(dbo.C.Amount) AS  Expr2, SUM(dbo.D.Amount) AS Expr3
FROM         dbo.A INNER JOIN
                  dbo.B ON dbo.A.Id = dbo.B.ExtId INNER JOIN
                  dbo.C ON dbo.A.Id = dbo.C.ExtId INNER JOIN
                  dbo.D ON dbo.A.Id = dbo.D.ExtId
GROUP BY dbo.A.Id, dbo.A.Total

Thanks in advance, its just that I hate SQL (or that SQL hates me).

EDIT: I had a typo. This query is not giving the right results. Extended the example.

Answer 1

Or you can take advantage of using SubQueries:

select A.ID, A.Total, b.SB as AmountB, c.SC as AmountC, d.SD as AmountD
from A
  inner join (select ExtID, sum(Amount) as SB from B group by ExtID) b on A.ID = b.ExtID
  inner join (select ExtID, sum(Amount) as SC from C group by ExtID) c on c.ExtID = A.ID
  inner join (select ExtID, sum(Amount) as SD from D group by ExtID) d on d.ExtID = A.ID

Answer 2

This might help other users.

SELECT Total=(Select Sum(Amount) from table a)+(Select Sum(Amount) from table b)+(Select Sum(Amount) from table c)

Answer 3

This one also works well

SELECT (SELECT SUM(Amount) FROM TableA) AS AmountA, (SELECT SUM(Amount) FROM TableB) AS AmountB, (SELECT SUM(Amount) FROM TableC) AS AmountC, (SELECT SUM(Amount) FROM TableD) AS AmountD

Answer 4

From your description, this query should give you an error as you are using the non-existent column dbo.A.Amount in your group by. Changing this to dbo.A.Total might be what you need.

If you need all the amounts together, then try this query:

select A.Id, A.Total, sum(B.Amount + C.Amount + D.Amount) AS Total_Amount
from A
  inner join B on A.Id = B.ExtId
  inner join C on A.Id = C.ExtId
  inner join D on A.Id = D.ExtId
group by A.Id, A.Total;

Answer 5

Try this code SELECT Total=isnull((Select Sum(Isnull(Amount,0)) from table a),0)+isnull((Select Sum(isnull(Amount,0)) from table b),0)+isnull((Select Sum(isnull(Amount,0)) from table c),0)