I am looking for a query to be able to identify the top-selling items (quantity) per location and to display details about that item and store. My tables are currently organized as such:
Stores
StoreID | Location | StoreName |
---|---|---|
1 | San Diego | Ralph’s |
2 | San Francisco | Ralph’s |
3 | Sacramento | Ralph’s |
Products
ProductID | Name | Category |
---|---|---|
1 | Milk | Food |
2 | Eggs | Food |
3 | Reese’s | Candy |
4 | Hershey’s | Candy |
Sales
SaleID | ProductID | StoreID | Quantity |
---|---|---|---|
1 | 1 | 1 | 4 |
2 | 1 | 1 | 2 |
3 | 2 | 2 | 2 |
4 | 3 | 3 | 3 |
Therefore, the result would return: Result:
StoreID | StoreLocation | ProductID | ProductName | Quantity |
---|---|---|---|---|
1 | San Diego | 1 | Milk | 6 |
2 | San Francisco | 2 | Eggs | 2 |
3 | Sacramento | 3 | Reese’s | 3 |
My current query I have sum’s the quantity totals. I figure I must use a MAX to achieve the highest in each category, but am not sure how to bring back the additional information so it displays more than the count and StoreID. I am running this in SMSS.
SELECT StoreID, StoreLocation, ProductID, ProductName, SUM(Quantity) AS Quantity FROM STORE INNER JOIN (Products INNER JOIN Sales ON Product.ProductID=Sales.ProductID) ON Store.StoreID=Sales.StoreID
Thank you for any advice on where to go next with this.
Links to tables:
https://freeimage.host/i/Q4XNp9
https://freeimage.host/i/Q4Xwk7
Advertisement
Answer
select StoreID ,Location ,ProductID ,Name ,Quantity from ( select * ,rank() over(partition by StoreID order by quantity desc) as rnk from ( select s.StoreID ,st.Location ,s.ProductID ,p.Name ,sum(Quantity) as Quantity from sales s join products p on p.ProductID = s.ProductID join stores st on st.StoreID = s.StoreID group by s.StoreID, st.Location, s.ProductID, p.Name ) t ) t where rnk = 1
StoreID | Location | ProductID | Name | Quantity |
---|---|---|---|---|
1 | San Diego | 1 | Milk | 6 |
2 | San Francisco | 2 | Eggs | 2 |
3 | Sacramento | 3 | Reese’s | 3 |