How to Find the Top N Items Sold at Each Store Using a Single SQL Query?

SQL Query to Determine Top N Items for Each Store

To find the top N items sold for each store in a single query, we can leverage a combination of the GROUP BY and ROW_NUMBER() functions. Here's a comprehensive solution:

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;

Understanding the Query

• The subquery s2 calculates the total sales (tds) for each item (UPCCode) within each store (StoreID).
• The outer query, using a WITH statement, creates a common table expression (CTE) named s. Within this CTE, ROW_NUMBER() is applied to rank the items within each store based on total sales in descending order (DESC).
• The final query selects the top 5 items (where rn is less than or equal to 5) for each store, sorting the results by store ID and total sales in descending order.

This approach efficiently retrieves the top-selling items for each store in a single SQL statement, avoiding the need for multiple queries or inefficient UNION operations.

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