Summarising multiple table data in a view

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

Summarising multiple table data in a view

Author

Topic

vegasbaby
Starting Member

2 Posts

Posted - 2008-11-24 : 19:51:56

Hi all,
VERY new to SQL server (MS Access background), so please excuse the question. I am not looking for an exact solution, but rather the best way to approach the problem.

I have 4 tables, 1 of which (Items) is a master table, and 3 (Movements, SalesOrders, PurchaseOrders) are transaction tables.

My desired output is a pivot table report in Excel (2007), so I want to create a single SQL View which has all the data. My view needs the following fields:
Item Number (from Items table), plus some other fields from this table
Total Movements (Sum of Quantity from Movements table)
Total Sales Orders (Sum of Quantity from SalesOrders table)
Total Purchase Orders (Sum of Quantity from PurchaseOrders table)

All tables share a key of ItemID.

In Access, I would create 3 separate summary queries for each of the 3 transaction tables, and then a 4 query joining the results of the 3 queries with the master table.

Would my SQL Server 2005 approach be the same, instead using Views instead of Queries, or can I do it all in a single view?

Any pointers are greatly appreciated.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-24 : 21:03:09

Just one query will be fine, query the master table and get the totals with subqueries, something like this:

SELECT ItemNumber, ItemCol2, ItemCol3
    , (SELECT sum(Quantity) FROM Movements WHERE Movement.ItemNumber = Items.ItemNumber) AS MovementTotal
    , (SELECT sum(Quantity) FROM SalesOrders WHERE SalesOrders.ItemNumber = Items.ItemNumber) AS SalesTotal
    , (SELECT sum(Quantity) FROM PurchaseOrders WHERE PurchaseOrders.ItemNumber = Items.ItemNumber) AS PurchaseTotal
FROM Items

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-24 : 22:28:04

quote:

Originally posted by snSQL

Just one query will be fine, query the master table and get the totals with subqueries, something like this:

Create view dbo.Vwsummarize
as
SELECT It.ItemNumber, It.ItemCol2, It.ItemCol3
,sum(mv.Quantity) ,sum(od.Quantity),sum(po.Quantity) FROM Item It
inner join Movements mv
on It.Itemid = mv.Itemid
inner join Orders od
on mv.Itemid = od.Itemid
inner join Purchaseorders po
on od.Itemid = po.Itemid
group by It.ItemNumber, It.ItemCol2, It.ItemCol3

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-24 : 23:14:11

[code]Create view dbo.Vwsummarize
as
SELECT It.ItemNumber, It.ItemCol2, It.ItemCol3
,mv.Quantity ,od.Quantity,po.Quantity
FROM Item It
OUTER APPLY (SELECT SUM(Quantity) AS Quantity
FROM Movements
WHERE Itemid = It.Itemid) mv
OUTER APPLY (SELECT SUM(Quantity) AS Quantity
FROM Orders
WHERE Itemid = It.Itemid) od
OUTER APPLY (SELECT SUM(Quantity) AS Quantity
FROM Purchaseorders
WHERE Itemid = It.Itemid) po[/code]

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2008-11-24 : 23:25:10

quote:

Originally posted by sodeep

Create view dbo.Vwsummarize
as
SELECT It.ItemNumber, It.ItemCol2, It.ItemCol3
,sum(mv.Quantity) ,sum(od.Quantity),sum(po.Quantity) FROM Item It
inner join Movements mv
on It.Itemid = mv.Itemid
inner join Orders od
on mv.Itemid = od.Itemid
inner join Purchaseorders po
on od.Itemid = po.Itemid
group by It.ItemNumber, It.ItemCol2, It.ItemCol3

Sodeep, this will not work because you will get rows repeated, each Movement row will be repeated for each Orders row and they'll all be repeated for every Purchase row and so on. So the number of rows in the result will be correct, but the totals will be higher than they should be.

vegasbaby
Starting Member

2 Posts

Posted - 2008-11-24 : 23:35:55

Hi guys,

Thanks for the quick response... I have tried snSQL's first response and it is giving the numbers I require (I am replicating a report, so I can easily check the accuracy).

I know this is stating the obvious, but as an SQL newbie I am experiencing this for the first time, but #$%@!!! (in a good way), SQL Server 2005 is quick!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources