How to Calculate Conditional Sums within a Grouped SQL Query (MSSQL)?

Using conditional summation in Group By query - MSSQL

Freddie needs a SQL query to summarize table data based on specific criteria. The original table OrderDetails contains four columns representing the costs associated with a specific order (OrderId). The goal is to create a new table with five columns: OrderId, ItemCount, TotalCopyCost, and TotalFullPrice.

To obtain the desired result, a combination of Group By and CASE statements are used. Group By is used to group the data by OrderId, while the CASE statement conditionally sums the CopyCost and FullPrice values ​​based on the PriceType column:

<code class="language-sql">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;</code>

This query effectively groups the OrderDetails table by OrderId, counts the number of items in each group, and conditionally sums the CopyCost and FullPrice values ​​for each group, resulting in a new table with the desired schema .

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