SQL Server Interview Question #90

What is conditional aggregation?

Aggregate Functions, GROUP BY & Window Functions Mid-Level Intermediate

Detailed Explanation

Conditional aggregation uses expressions such as CASE inside aggregate functions to calculate multiple conditional metrics in a single grouped query.

It is particularly useful for dashboards—for example, calculating total cases, CPO cases, non-CPO cases, open cases, and closed cases without issuing separate queries for each metric.

Code Example

SELECT
    COUNT(*) AS TotalCases,
    SUM(CASE WHEN CPO = 1 THEN 1 ELSE 0 END) AS CpoCases,
    SUM(CASE WHEN CPO = 0 THEN 1 ELSE 0 END) AS NonCpoCases
FROM dbo.CaseReports;