SQL SUM from 3 Tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL SUM from 3 Tables

Author

Topic

piragash
Starting Member

8 Posts

Posted - 2012-03-03 : 01:30:16

Table1
Code Name
1001 Apple
1002 Orange
1003 Pear
1004 Durian

Table2
Code ReceiveQty
1001 5
1001 6
1002 3
1003 5
1003 6
1003 2
1004 5

Table3
Code StockOutQty
1001 5
1001 2
1001 2
1002 1
1003 2
1003 3
1003 2
Query Result
Code Name ReceiveQty StockOutQty
1001 Apple 11 9
1002 Orange 3 1
1003 Pear 13 7
1004 Durian 5 0

Question: How to write a SQL SUM Query to get data from 3 different tables output as "Query Result"

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-03 : 02:17:44

select A.Code, A.Name, SUM(B.ReceiveQty) as ReceiveQty, SUM(C.StockOutQty) as StockOutQty
from Table1 A
join Table2 B ON b.code=a.code
join Table3 C ON c.code=a.code
group by A.Code, A.Name

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2012-03-03 : 16:16:03

Piragash, I took a second look at this and realized I made a couple mistakes. First, you need a left outer join for your tables 2 and 3. Second, we need to either use a Sum(Distinct ReceiveQty), Sum(Distinct StockOutQty) or do sub queries like this:

select a.code, a.name, coalesce(b.sumB,0) as ReceiveQty, Coalesce(c.sumC,0) as StockOutQty
from table1 A
left join (select code, SUM(receiveqty) as sumB
from table2
group by code) B on B.code=A.code
left join (select code, SUM(stockoutqty) as sumC
from table3
group by code) C on c.code=A.code

That should produce the desired result

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:48:05

[code]
SELECT t1.Code,t1.name,SUM(ReceiveQty) AS ReceiveQty,SUM(StockOutQty) AS StockOutQty
FROM table1
LEFT JOIN (SELECT Code, SUM(ReceiveQty) AS ReceiveQty,CAST(0 as varchar(10)) AS StockOutQty
FROM table2
GROUP BY Code

UNION ALL

SELECT Code, CAST(0 AS varchar(10)),SUM(StockOutQty)
FROM table3
GROUP BY Code
)t2
ON t2.Code = t1.Code
GROUP BY t1.Code,t1.name
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

piragash
Starting Member

8 Posts

Posted - 2012-03-04 : 23:12:51

Hi sir, thank you very much for the query code and i manage to solve my problem from your code given.

Thanks again.

quote:
Originally posted by flamblaster

Piragash, I took a second look at this and realized I made a couple mistakes. First, you need a left outer join for your tables 2 and 3. Second, we need to either use a Sum(Distinct ReceiveQty), Sum(Distinct StockOutQty) or do sub queries like this:

select a.code, a.name, coalesce(b.sumB,0) as ReceiveQty, Coalesce(c.sumC,0) as StockOutQty
from table1 A
left join (select code, SUM(receiveqty) as sumB
from table2
group by code) B on B.code=A.code
left join (select code, SUM(stockoutqty) as sumC
from table3
group by code) C on c.code=A.code

That should produce the desired result

piragash
Starting Member

8 Posts

Posted - 2012-03-04 : 23:16:39

Hi sir, thank you very much for the query code. Both code Mr. Flamblaster and your code given manage to solve my problem.

Thanks again.

quote:

Originally posted by visakh16


SELECT t1.Code,t1.name,SUM(ReceiveQty) AS ReceiveQty,SUM(StockOutQty) AS StockOutQty
FROM table1
LEFT JOIN (SELECT Code,	SUM(ReceiveQty) AS ReceiveQty,CAST(0 as varchar(10)) AS StockOutQty
           FROM table2
           GROUP BY Code

           UNION ALL
           
           SELECT Code,	CAST(0 AS varchar(10)),SUM(StockOutQty)
           FROM table3
           GROUP BY Code
          )t2
ON t2.Code = t1.Code
GROUP BY t1.Code,t1.name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

piragash
Starting Member

8 Posts

Posted - 2012-03-04 : 23:25:15

Hi sir, thank you very much for the query code. Both code Mr. Flamblaster and your code given manage to solve my problem.

Thanks again.

quote:

Originally posted by visakh16


SELECT t1.Code,t1.name,SUM(ReceiveQty) AS ReceiveQty,SUM(StockOutQty) AS StockOutQty
FROM table1
LEFT JOIN (SELECT Code,	SUM(ReceiveQty) AS ReceiveQty,CAST(0 as varchar(10)) AS StockOutQty
           FROM table2
           GROUP BY Code

           UNION ALL
           
           SELECT Code,	CAST(0 AS varchar(10)),SUM(StockOutQty)
           FROM table3
           GROUP BY Code
          )t2
ON t2.Code = t1.Code
GROUP BY t1.Code,t1.name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

piragash
Starting Member

8 Posts

Posted - 2012-03-05 : 21:30:07

The SQL query code below working fine, but with small problem which it’s list out all item belong to table 1 even do not have any transaction data in table 2 (ReceiveQty) and table 3 (StockOutQty). I only want to list out item which exist in table 2 and table 3 and want like query result table. Table 1 Code 1005 ~ 1007 do not have any transaction and no need to list out. Sorry for trouble you again.

Table1
Code Name
1001 Apple
1002 Orange
1003 Pear
1004 Durian
1005 Banana
1006 Cherry
1007 Kiwi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:23:24

just make a small change as


SELECT t1.Code,t1.name,SUM(ReceiveQty) AS ReceiveQty,SUM(StockOutQty) AS StockOutQty
FROM table1
INNER JOIN (SELECT Code,	SUM(ReceiveQty) AS ReceiveQty,CAST(0 as varchar(10)) AS StockOutQty
           FROM table2
           GROUP BY Code

           UNION ALL
           
           SELECT Code,	CAST(0 AS varchar(10)),SUM(StockOutQty)
           FROM table3
           GROUP BY Code
          )t2
ON t2.Code = t1.Code
GROUP BY t1.Code,t1.name

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources