SUM from multiple 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

SQL Server 2005 Forums

Transact-SQL (2005)

SUM from multiple tables

Author

Topic

Nls
Starting Member

2 Posts

Posted - 2009-09-25 : 17:31:34

Hello, thanks but I am not quite sure I understand it.
Let me take a new example.
I have 4 tables:
Items
Orders
Purchase
Warehouse
I want to sum, for every item in itemstable, quantity in orders, purchase and warehousetable.
I believe there must be a derived table involved but I'm not sure about the syntax
See attached image for an explanation with tabledata.

[url]http://www.tomasbredh.se/tables.jpg[/url]

rekiller
Starting Member

31 Posts

Posted - 2009-09-25 : 18:25:21

[code]
DECLARE @ITEMS TABLE (ItemNumber Char(1))
DECLARE @ORDERS TABLE (ItemNumber Char(1), Quantity Integer)
DECLARE @PURCHASE TABLE (ItemNumber Char(1), Quantity Integer)
DECLARE @WAREHOUSE TABLE (ItemNumber Char(1), Quantity Integer)

INSERT INTO @ITEMS
SELECT 'A'
UNION
SELECT 'B'
UNION
SELECT 'C'

INSERT INTO @ORDERS
SELECT 'B',4
UNION
SELECT 'A',1
UNION
SELECT 'A',3
UNION
SELECT 'C',6
UNION
SELECT 'B',2

INSERT INTO @PURCHASE
SELECT 'B',6
UNION
SELECT 'B',2
UNION
SELECT 'C',4
UNION
SELECT 'C',33
UNION
SELECT 'A',1

INSERT INTO @WAREHOUSE
SELECT 'A',4
UNION
SELECT 'A',1
UNION
SELECT 'A',3
UNION
SELECT 'C',6
UNION
SELECT 'B',3

SELECT A.ItemNumber, ISNULL(( B.Quantity ),0) QOrders, ISNULL(( C.Quantity ),0) QPurchase, ISNULL(( D.Quantity ),0) QWareHouse
FROM @ITEMS A
LEFT JOIN
(SELECT ItemNumber, SUM(Quantity) Quantity
FROM @ORDERS
GROUP BY ItemNumber) B ON A.ItemNumber = B.ItemNumbeR
LEFT JOIN
(SELECT ItemNumber, SUM(Quantity) Quantity
FROM @PURCHASE
GROUP BY ItemNumber) C ON A.ItemNumber = C.ItemNumbeR
LEFT JOIN
(SELECT ItemNumber, SUM(Quantity) Quantity
FROM @WAREHOUSE
GROUP BY ItemNumber) D ON A.ItemNumber = D.ItemNumbeR

[/code]

Nls
Starting Member

2 Posts

Posted - 2009-09-26 : 13:39:22

Works great! Thanks alot!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources