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

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

CodePudding user response：

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