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.

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

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