Find Products with Sales Above the Average Sales of All Products

Find Products with Sales Above the Average Sales of All Products Query Using MS SQL Server?

To find products with sales above the average sales of all products, you need to follow these steps:

1. Calculate the total sales for each product.
2. Compute the average sales across all products.
3. Filter products that have sales above this average.

Assuming you have the following tables:

• OrderDetails:
  • OrderID
  • ProductID
  • Quantity
  • UnitPrice (price per unit of the product)

Here’s how you can write the SQL query:

SQL Query

WITH ProductSales AS (

    SELECT

        ProductID,

        SUM(Quantity * UnitPrice) AS TotalSales

    FROM OrderDetails

    GROUP BY ProductID

),

AverageSales AS (

    SELECT

        AVG(TotalSales) AS AvgSales

    FROM ProductSales

)

SELECT

    ps.ProductID,

    ps.TotalSales

FROM ProductSales ps

JOIN AverageSales asales

    ON ps.TotalSales > asales.AvgSales;

Explanation:

1. CTE ProductSales:

   • SUM(Quantity * UnitPrice) AS TotalSales: Calculates the total sales amount for each product by multiplying quantity by unit price and summing it up.
   • GROUP BY ProductID: Groups the results by product to get the total sales for each product.

2. CTE AverageSales:

   • AVG(TotalSales) AS AvgSales: Computes the average sales amount across all products from the ProductSales CTE.

3. Final Query:

   • JOIN AverageSales asales ON ps.TotalSales > asales.AvgSales: Filters products whose total sales exceed the average sales amount.

Example Data and Results

Assume the OrderDetails table has the following data:

OrderID	ProductID	Quantity	UnitPrice
1	A	10	100
2	B	5	200
3	A	3	100
4	C	8	150
5	B	7	200

The ProductSales CTE would produce:

ProductID	TotalSales
A	1300
B	2400
C	1200

The AverageSales CTE would calculate:

AvgSales
1633.33

The final query result would be:

ProductID	TotalSales
B	2400

In this case, Product B has total sales above the average sales amount.

You can execute this query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to find products with sales above the average sales of all products.