Conditional aggregation

This lesson uses the unmodified DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Count several order statuses in one query over the same rows.

Run the example

SELECT COUNT(*) AS TotalOrders,
       SUM(CASE WHEN OrderStatus = N'Completed' THEN 1 ELSE 0 END) AS CompletedOrders,
       SUM(CASE WHEN OrderStatus = N'Cancelled' THEN 1 ELSE 0 END) AS CancelledOrders
FROM dbo.Orders;

Read it step by step

1. COUNT(*) counts every order.
2. Each CASE returns 1 for its chosen status and 0 otherwise.
3. SUM adds those indicators, producing two counts over the same 5,000 rows.

Check the result

CompletedOrders and CancelledOrders are separate subsets; they do not cover every status.

Practice

Count Online orders and Online orders with Completed status in one result row.

SELECT COUNT(*) AS OnlineOrders,
       SUM(CASE WHEN OrderStatus = N'Completed' THEN 1 ELSE 0 END) AS CompletedOnlineOrders
FROM dbo.Orders
WHERE OrderChannel = N'Online';