SQL Server Interview Question #90

What is conditional aggregation?

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

Quick Interview Answer

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.

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;