MySQL Query to Calculate Product Sales Percentage

Calculate the Percentage of Total Sales for each Product

Write a MySQL query to calculate the percentage of total sales for each product using a window function.

Solution:

-- Begin the SELECT statement to define the columns to retrieve from the ProductSales table
SELECT 
    -- Retrieve the ProductID column to uniquely identify each product
    ProductID, 
    -- Retrieve the Sales column to display the sales amount for each product
    Sales,
    -- Calculate the sales percentage for each product:
    -- SUM(Sales) OVER () computes the total sales across all products
    -- Dividing the product's Sales by the total sales and multiplying by 100 gives the percentage contribution
    Sales / SUM(Sales) OVER () * 100 AS SalesPercentage
-- Specify the source table containing product sales data
FROM ProductSales;

Explanation:

• Purpose of the Query:
• The goal is to calculate the percentage of total sales contributed by each product.
• This demonstrates the use of the SUM() window function to compute the total sales.
• Key Components:
• SUM(Sales) OVER (): Calculates the total sum of sales.
• Sales / SUM(Sales) OVER () * 100: Computes the percentage of total sales for each product.
• Why use Window Functions?:
• Window functions allow you to perform calculations across the entire result set, making it easier to compute percentages and other aggregate metrics.
• Real-World Application:
• For example, in a retail business, you might want to analyze the contribution of each product to the overall sales to identify best-sellers and underperformers.

For more Practice: Solve these Related Problems:

• Write a MySQL query to calculate the percentage of total sales within each product category.
• Write a MySQL query to find products that contribute to more than 20% of total sales.
• Write a MySQL query to compute the percentage of total revenue each product contributed in the last 3 months.
• Write a MySQL query to calculate the cumulative percentage of sales, ranked from highest to lowest.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Find the First and Last Sale Date for Each Customer.
Next MySQL Exercise: Find the Median Salary in Each Department.