Retrieve no. of repeated items

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

Retrieve no. of repeated items

Author

Topic

Notoook
Starting Member

18 Posts

Posted - 2009-08-27 : 07:27:15

Hi,

I've 3 tables, Stores, Items, and Store_Item which links between stores and items.

now i'm able to get the count of each item per store, what i want to do is get if an item is repeated in other stores & for how many times it is repeated for another store / stores.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-08-27 : 09:51:47

[code]SELECT i.ItemName,
StoreName,
COUNT(*) AS ItemCount,
CASE WHEN scnt.OtherStoreCount=0 THEN 'No Repeated' ELSE 'Repeated' END AS OtherStoreInfo,
scnt.OtherStoreCount
FROM Item i
JOIN Store_Item si
ON si.ItemID=i.ItemID
JOIN Store s
ON s.StoreID=si.StoreID
CROSS APPLY (SELECT COUNT(DISTINCT StoreID) AS OtherStoreCount
FROM Store_Item
WHERE ItemID=i.ItemID
AND StoreID <> s.StoreID)scnt
GROUP BY i.ItemName,s.StoreName[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources