CODEWITHSUNDEEP
sqlselectjoinsum
Ignacio Soler Garcia
Ignacio Soler Garcia

Reputation: 21855

SQL Query to sum fields from different tables

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.

Upvotes: 9

Views: 56718

Answers (5)

mitul
mitul

Reputation: 1

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)

Upvotes: -1

user3127648
user3127648

Reputation: 1377

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)

Upvotes: 0

Jahson kyalo
Jahson kyalo

Reputation: 301

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

Upvotes: 2

odiseh
odiseh

Reputation: 26547

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

Upvotes: 30

a'r
a'r

Reputation: 37009

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;

Upvotes: 3

Related Questions