Calculate the Percentage of Total Sales for Each Product SQL Server

Calculate the Percentage of Total Sales for Each Product Query Using MS SQL Server?

To calculate the percentage of total sales for each product in SQL Server, you need to perform the following steps:

1. Calculate the total sales amount for each product.
2. Calculate the total sales amount across all products.
3. Compute the percentage of total sales for each product based on the total sales.

Assuming you have a table named Sales with the following columns:

• ProductID
• Amount (the sales amount for each transaction)

Here's a step-by-step SQL query to achieve this:

SQL Query

WITH TotalSales AS (

    SELECT

        ProductID,

        SUM(Amount) AS ProductTotal

    FROM Sales

    GROUP BY ProductID

),

OverallTotal AS (

    SELECT

        SUM(ProductTotal) AS TotalAmount

    FROM TotalSales

)

SELECT

    t.ProductID,

    t.ProductTotal,

    (t.ProductTotal / o.TotalAmount * 100) AS PercentageOfTotalSales

FROM TotalSales t

CROSS JOIN OverallTotal o

ORDER BY t.ProductID;

Explanation:

1. CTE TotalSales:

   • Calculates the total sales amount for each product.
   • SUM(Amount) AS ProductTotal: Sums up the sales amounts grouped by ProductID.

2. CTE OverallTotal:

   • Computes the total sales amount across all products.
   • SUM(ProductTotal) AS TotalAmount: Sums up the product totals from the TotalSales CTE.

3. Final Query:

   • CROSS JOIN OverallTotal o: Joins each product’s total with the overall total to calculate the percentage.
   • (t.ProductTotal / o.TotalAmount * 100) AS PercentageOfTotalSales: Computes the percentage of total sales for each product.

Example Data and Results

Assume the Sales table has the following data:

ProductID	Amount
1	100
1	150
2	200
2	50
3	300

The TotalSales CTE will produce:

ProductID	ProductTotal
1	250
2	250
3	300

The OverallTotal CTE will produce:

TotalAmount
800

The final query result will be:

ProductID	ProductTotal	PercentageOfTotalSales
1	250	31.25
2	250	31.25
3	300	37.50

This result shows the percentage of total sales contributed by each product.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to get the percentage of total sales for each product.

https://mytecbooks.blogspot.com/2024/08/calculate-percentage-of-total-sales-for.html

Calculate the Percentage of Total Sales for Each Product Query Using MS SQL Server programs interview questions and answers freshers experienced