Get the Highest and Lowest Sale Amount for Each Product Query Using MS SQL Server?

To retrieve the highest and lowest sale amount for each product using Microsoft SQL Server, you need to:

1. Aggregate sale amounts for each product to determine the highest and lowest sale amount.
2. Group by product to calculate these values.

Assuming you have the following tables:

• OrderItems table with columns: OrderID, ProductID, Quantity, UnitPrice.
• Sales table with columns: OrderID, SaleAmount (this might be redundant if SaleAmount is computed from Quantity and UnitPrice).

Here's how you can write a query to find the highest and lowest sale amount for each product:

SQL Query

-- Find the highest and lowest sale amount for each product

WITH SaleAmounts AS (

    SELECT

        oi.ProductID,

        oi.UnitPrice * oi.Quantity AS SaleAmount

    FROM

        OrderItems oi

),

ProductSales AS (

    SELECT

        sa.ProductID,

        MAX(sa.SaleAmount) AS MaxSaleAmount,

        MIN(sa.SaleAmount) AS MinSaleAmount

    FROM

        SaleAmounts sa

    GROUP BY

        sa.ProductID

)

SELECT

    p.ProductID,

    p.ProductName, -- Assuming you have a Products table with ProductName

    ps.MaxSaleAmount,

    ps.MinSaleAmount

FROM

    ProductSales ps

    JOIN Products p ON ps.ProductID = p.ProductID

ORDER BY

    p.ProductID;

Explanation

1. SaleAmounts CTE:

   • oi.UnitPrice * oi.Quantity AS SaleAmount: Calculates the sale amount for each item in an order.
   • This assumes that SaleAmount is not directly stored but needs to be calculated as UnitPrice * Quantity.

2. ProductSales CTE:

   • MAX(sa.SaleAmount) AS MaxSaleAmount: Finds the maximum sale amount for each product.
   • MIN(sa.SaleAmount) AS MinSaleAmount: Finds the minimum sale amount for each product.
   • GROUP BY sa.ProductID: Groups by ProductID to compute the highest and lowest sale amounts for each product.

3. Main Query:

   • JOIN Products p ON ps.ProductID = p.ProductID: Joins with the Products table to get additional details like ProductName.
   • ORDER BY p.ProductID: Orders the results by ProductID for better readability.

Additional Considerations

• Data Accuracy: Ensure that the OrderItems table accurately reflects all sales and that UnitPrice and Quantity are correctly populated.
• Handling Missing Data: If some products have no sales, they won't appear in the results. Handle such cases if necessary.
• Indexes: Indexes on ProductID, UnitPrice, and Quantity can help improve query performance.

Adjust column names and table structures based on your actual schema. This query will effectively provide the highest and lowest sale amount for each product.