How to Calculate Conditional Sums within a Group By Query in MSSQL?

Conditional summation in MSSQL grouped query

In SQL Server's OrderDetails table, each order item has a unique OrderId, and associated cost (CopyCost, FullPrice) and price type (PriceType). The goal is to create a summary table with three columns: OrderId, ItemCount, TotalCopyCost, and TotalFullPrice, where ItemCount is the number of items per order, TotalCopyCost is the sum of the CopyCost values ​​for each order, and TotalFullPrice is the sum of the FullPrice values ​​for each order. .

For this, you can use a grouped query with conditional sum:

SELECT OrderId, 
       COUNT(*) AS ItemCount,
       SUM(CASE WHEN PriceType = 'CopyCost' THEN Price ELSE 0 END) AS TotalCopyCost,
       SUM(CASE WHEN PriceType = 'FullPrice' THEN Price ELSE 0 END) AS TotalFullPrice
  FROM OrderDetails
 GROUP BY OrderId

In this query:

• COUNT(*) expression calculates ItemCount.
• Two SUM(CASE) expressions perform a conditional sum based on the PriceType column. If PriceType is CopyCost, the CASE expression evaluates to Price itself; otherwise, it evaluates to 0. The SUM function then adds the values ​​of each order. Similarly, the second CASE expression handles the summation of FullPrice.

By using conditional summation, the query efficiently calculates the total cost and full price of each order and groups the results by OrderId. The resulting summary table provides a comprehensive view of the raw data, making it easier to analyze order-related charges.

The above is the detailed content of How to Calculate Conditional Sums within a Group By Query in MSSQL?. For more information, please follow other related articles on the PHP Chinese website!