How to Retrieve the Top N Sold Items per Store Using a Single SQL Query?

SQL Query to Retrieve Top N Items Sold Per Group

In this question, a user seeks a SQL query to retrieve the top 5 sold items for each store in a single query. The provided table, Sales, contains columns such as UPCCode, SaleDate, StoreId, and TotalDollarSales.

In order to achieve the desired result, we can utilize a combination of subqueries and partitioning within a single SQL query. Here's a detailed breakdown:

1. Subquery to Calculate Sum of Sales for Each UPCCode:

   • Calculate the sum of TotalDollarSales for each unique combination of StoreId and UPCCode.

2. Add Row Number to Partitioned Results:

   • Partition the results by StoreId and assign a row number to each row in descending order of TotalDollarSales.

3. Select Top N Items for Each Store:

   • Filter the results to include only rows where the row number is less than or equal to 5. This will provide the top 5 items for each store.

4. Retrieve Final Results:

   • Select the StoreId, UPCCode, and TotalDollarSales for the top 5 items for each store.

Here's the complete SQL query that combines these steps:

WITH s AS (
  SELECT StoreID, UPCCode, tds, rn = ROW_NUMBER()
  OVER (PARTITION BY StoreID ORDER BY tds DESC)
  FROM (
    SELECT StoreID, UPCCode, tds = SUM(TotalDollarSales)
    FROM Sales
    GROUP BY StoreID, UPCCode
  ) AS s2
)
SELECT StoreID, UPCCode, TotalDollarSales = tds
FROM s
WHERE rn <= 5
ORDER BY StoreID, TotalDollarSales DESC;

The above is the detailed content of How to Retrieve the Top N Sold Items per Store Using a Single SQL Query?. For more information, please follow other related articles on the PHP Chinese website!