Skip to content
Advertisement

SQL Query to find top item of each group and display details from other tables

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

Fiddle

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement