Selecting usage by store. - 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 2000 Forums

SQL Server Development (2000)

Selecting usage by store.

Author

Topic

Smitty200
Starting Member

11 Posts

Posted - 2006-05-09 : 09:56:10

Let me start by sayin I am capable of your basic selects, inserts and updates, but this query has me stumped.

I have 4 tables. tItems, tStores, tOrders, tOrdersDetail.

tItems
--sku
--description

tStores
--storeid

tOrders
--OrderID
--storeID

tOrdersDetail
--OrderID
--sku
--quantity

I am able to write a query that produces columns

SKU StoreID Sum(quantity)

But I would like my query to return

SKU Store1 Store2 Store3....

where each row would be the sku and usage by store.

I am able to do this query for a single store doing the following

Select p.SKU, Sum(od.quantity) as Store1
FROM tItems p, tStores s, tOrders o, tOrdersDetail od
WHERE p.sku = od.sku
AND od.OrderID = o.OrderID
AND o.storeID = s.storeID
AND s.storeID = 'store1'

This query works and returns the proper values. I then try and add a second store by adding the same tables a second time with different alliases used like so.

Select p.SKU, Sum(od.quantity) as Store1, Sum(od2.quantity) as Store2
FROM tItems p, tStores s, tOrders o, tOrdersDetail od,
tItems p2, tStores s2, tOrders o2, tOrdersDetail od2
WHERE p.sku = od.sku
AND od.OrderID = o.OrderID
AND o.storeID = s.storeID
AND s.storeID = 'store1'
--2
AND p2.sku = od2.sku
AND od2.OrderID = o2.OrderID
AND o2.storeID = s2.storeID
AND s2.storeID = 'store1'
Group by p.SKU

The second query returns double the values for each sku for store 1 and is quadrupling the sums for each sku for store2. I know there is something wrong with my grouping but can not figure it out. I am also wondering if there is a better way of doing this query since there are 10 stores and I can't imagine aliasing the tables 10 times. Any help, suggested articles, or direction would be appreciated.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-09 : 13:18:08

Here is one method

I only did three stores but you should understand

Set nocount on

SELECT dbo.tOrdersDetail.SKU, SUM(dbo.tOrdersDetail.Quanity) AS Store1
INTO #Store1
FROM dbo.tOrdersDetail INNER JOIN
dbo.tOrders ON dbo.tOrdersDetail.OderID = dbo.tOrders.OrderID
WHERE (dbo.tOrders.StoreID = 'Store1')
GROUP BY dbo.tOrdersDetail.SKU

SELECT dbo.tOrdersDetail.SKU, SUM(dbo.tOrdersDetail.Quanity) AS Store2
INTO #Store2
FROM dbo.tOrdersDetail INNER JOIN
dbo.tOrders ON dbo.tOrdersDetail.OderID = dbo.tOrders.OrderID
WHERE (dbo.tOrders.StoreID = 'Store2')
GROUP BY dbo.tOrdersDetail.SKU

SELECT dbo.tOrdersDetail.SKU, SUM(dbo.tOrdersDetail.Quanity) AS Store3
INTO #Store3
FROM dbo.tOrdersDetail INNER JOIN
dbo.tOrders ON dbo.tOrdersDetail.OderID = dbo.tOrders.OrderID
WHERE (dbo.tOrders.StoreID = 'Store3')
GROUP BY dbo.tOrdersDetail.SKU

SELECT dbo.TItems.Sku, ISNULL(#Store1.Store1, 0) AS store1, ISNULL(#Store2.Store2, 0) AS store2, ISNULL(#Store3.Store3, 0) AS store3
FROM dbo.TItems LEFT OUTER JOIN
#Store3 ON dbo.TItems.Sku = #Store3.SKU LEFT OUTER JOIN
#Store2 ON dbo.TItems.Sku = #Store2.SKU LEFT OUTER JOIN
#Store1 ON dbo.TItems.Sku = #Store1.SKU

Jim
Users <> Logic

Smitty200
Starting Member

11 Posts

Posted - 2006-05-09 : 13:54:37

Thanks Jim, I really appreciate the help.

Smitty200
Starting Member

11 Posts

Posted - 2006-05-09 : 14:17:15

Jim, it seems that when I use the temp tables it can't find the #Store1.Store1. It tells me that Store1 is an Invalid Column name. If I remove the # signs and actually create the tables then it works. I was wondering if you would know why it can't find the columns in the temp tables? Sorry to be a pain.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-05-10 : 03:33:28

Try to use Alias name

Madhivanan

Failing to plan is Planning to fail

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 07:37:50

I'm thinking this technique is a bit simpler...

--data
declare @tOrders table (OrderID int, storeID int)
insert @tOrders
          select 1, 1
union all select 2, 2
union all select 3, 2
union all select 4, 3
union all select 5, 3
union all select 6, 3
union all select 7, 4
union all select 8, 4
union all select 9, 4
union all select 10, 4

declare @tOrdersDetail table (OrderID int, sku varchar(10), quantity int)
insert @tOrdersDetail
          select 1, 'A', 3
union all select 2, 'B', 5
union all select 3, 'A', 7
union all select 4, 'C', 9
union all select 5, 'C', 1
union all select 6, 'B', 2
union all select 7, 'D', 4
union all select 8, 'A', 6
union all select 9, 'E', 8
union all select 10, 'A', 10

--calculation
select sku,
    sum(case when StoreID = 1 then quantity else 0 end) as Store1,
    sum(case when StoreID = 2 then quantity else 0 end) as Store2,
    sum(case when StoreID = 3 then quantity else 0 end) as Store3,
    sum(case when StoreID = 4 then quantity else 0 end) as Store4
from @tOrders a inner join @tOrdersDetail b on a.OrderID = b.OrderID group by sku

Also, here's a useful link to do with pivoting data...

http://www.sqlteam.com/item.asp?ItemID=2955

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2006-05-10 : 07:52:25

Try Replaceing the last select with this

SELECT dbo.TItems.Sku, ISNULL(dbo.#Store1.Store1, 0) AS store1, ISNULL(dbo.#Store2.Store2, 0) AS store2, ISNULL(dbo.#Store3.Store3, 0) AS store3
FROM dbo.TItems LEFT OUTER JOIN
dbo.#Store3 ON dbo.TItems.Sku = dbo.#Store3.SKU LEFT OUTER JOIN
dbo.#Store2 ON dbo.TItems.Sku = dbo.#Store2.SKU LEFT OUTER JOIN
dbo.#Store1 ON dbo.TItems.Sku = dbo.#Store1.SKU

Jim
Users <> Logic

Smitty200
Starting Member

11 Posts

Posted - 2006-05-10 : 10:45:16

Thanks everyone for your input. Jim adding the dbo. worked. 2300 SKU's and 75 stores and the whole thing takes about 3 seconds to run. Thanks again. Makes displaying the data a breeze.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-10 : 10:57:57

Smitty200 - did you try the method I posted? It should run in much less 3 seconds...

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources