Conditional Summation with MSSQL's GROUP BY Clause
Microsoft SQL Server (MSSQL) offers flexible data aggregation using the GROUP BY
clause. To perform conditional summation within a GROUP BY
query, leverage the CASE
statement to selectively include values in the sum based on specific criteria.
Consider an OrderDetails
table with the following structure:
<code>---------------------------------------------------------------- | OrderId | CopyCost | FullPrice | Price | PriceType | ---------------------------------------------------------------- | 16 | 50 | 100 | 50 | CopyCost | ---------------------------------------------------------------- | 16 | 50 | 100 | 100 | FullPrice | ---------------------------------------------------------------- | 16 | 50 | 100 | 50 | CopyCost | ---------------------------------------------------------------- | 16 | 50 | 100 | 50 | CopyCost | ----------------------------------------------------------------</code>
To calculate the ItemCount
, TotalCopyCost
, and TotalFullPrice
for each OrderId
, use this query:
<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>
The CASE
statement within the SUM()
function conditionally sums Price
values. If PriceType
is 'CopyCost', the Price
is included; otherwise, 0 is added. This allows for separate totals for 'CopyCost' and 'FullPrice' per OrderId
.
The output would be:
<code>---------------------------------------------------------------- | OrderId | ItemCount | TotalCopyCost | TotalFullPrice | ---------------------------------------------------------------- | 16 | 4 | 150 | 100 | ----------------------------------------------------------------</code>
This demonstrates how CASE
statements enable powerful conditional aggregation within GROUP BY
queries, providing a flexible method for data summarization and analysis in MSSQL.
The above is the detailed content of How to Perform Conditional Summation in MSSQL Group By Queries?. For more information, please follow other related articles on the PHP Chinese website!