Related ticket counts do not need separate queries by default. Conditional aggregation can calculate the related counts in one grouped statement. Use CASE expressions that match the business rules, then verify the denominator and NULL behavior before sending the result to the dashboard.
Define the Categories Before Conditional Aggregation
Open, closed, and late are not necessarily mutually exclusive categories. A late ticket can also be open. Decide whether the report needs overlapping measures or a complete partition of the population. The SQL should express that choice directly rather than forcing the totals to look additive.
I fix the as-of time when testing due-date logic. A query using the current clock changes its late category as time passes. A fixed value makes the test reproducible and separates clock behavior from the counting rule. Production reports can still use the approved current-time convention.
The sample treats an open ticket with a due time before the as-of timestamp as late. A NULL due time is not classified as late under that condition. Decide whether missing due dates deserve a separate warning count. Dashboard labels are short. The rules behind them usually need more than one word.
Count Only the CASE Values You Intend
COUNT ignores NULL expressions. COUNT(CASE WHEN condition THEN 1 END) therefore counts only rows satisfying the condition, because the implicit ELSE returns NULL. SUM(CASE WHEN condition THEN 1 ELSE 0 END) adds one for matches and zero for other rows.
The following setup and grouped query use synthetic tickets. Each TeamID receives its own counts in one statement. The totals refer to all tickets in that team's input population. A final ORDER BY controls the report's presentation.
I keep TotalTickets beside the conditional counts during validation. That makes missing statuses and overlapping conditions visible. A report that contains only the desired counts can hide rows not assigned to any displayed category. Confirm the raw input before deciding whether a discrepancy reflects bad data or an intentional additional status. One grouped query is useful because the related measures share the same population and observation point.
CREATE TABLE #Tickets(TicketID int PRIMARY KEY,TeamID int,StatusName varchar(12),DueAt datetime2 NULL);
INSERT #Tickets VALUES(1,10,'Open','20260920'),(2,10,'Closed','20260920'),
(3,10,'Open',NULL),(4,20,'Held','20260920');
DECLARE @AsOf datetime2='20260924';
SELECT TeamID,COUNT(*) AS TotalTickets,
COUNT(CASE WHEN StatusName='Open' THEN 1 END) AS OpenTickets,
SUM(CASE WHEN StatusName='Closed' THEN 1 ELSE 0 END) AS ClosedTickets,
COUNT(CASE WHEN StatusName='Open' AND DueAt<@AsOf THEN 1 END) AS LateOpenTickets
FROM #Tickets GROUP BY TeamID ORDER BY TeamID;Demonstrate the ELSE Zero Trap
COUNT counts every non-NULL expression, including zero. Adding ELSE 0 to the CASE inside COUNT therefore makes every row count. That is a logic error even though the statement is valid T-SQL and its result looks like a plausible integer.
The next query puts the incorrect and correct forms beside each other. Inspect the output from your own execution and compare it with the known synthetic inputs. Do not memorize one result number; understand which values COUNT sees for qualifying and nonqualifying rows.
What expression reaches the aggregate when the condition is false? That question resolves most confusion here. COUNT needs NULL for a nonmatch, while SUM needs zero when you want a numeric contribution of nothing. Use the form that makes the intention clear to the next reviewer. COUNT_BIG provides the larger return type when the actual population can exceed COUNT's int range, but it follows the same NULL rule.
SELECT COUNT(CASE WHEN StatusName='Open' THEN 1 ELSE 0 END) AS IncorrectOpenCount,
COUNT(CASE WHEN StatusName='Open' THEN 1 END) AS CorrectOpenCount,
SUM(CASE WHEN StatusName='Open' THEN 1 ELSE 0 END) AS CorrectOpenSum
FROM #Tickets;
Put the Percentage Rule in the Query
An open percentage can mean open tickets divided by all tickets, or open tickets divided by a selected eligible subset. Those denominators are different business definitions. Label the output according to the chosen one and keep the denominator available during review.
The next query divides the open count by all input tickets. Multiplying by 100.0 preserves fractional arithmetic, and NULLIF protects a zero denominator. The grouped result contains only teams with input rows. To show teams with no tickets, start from the team table and use a carefully counted left join.
With a left join, COUNT(*) counts the preserved team row even when no ticket matched. Count the non-NULL ticket identifier for the actual ticket population instead. This is another case where the aggregate's expression matters more than its name. Empty groups, missing relationships, and overlapping categories need explicit tests before the percentage becomes a trusted report metric.
SELECT TeamID,COUNT(*) AS TotalTickets,
COUNT(CASE WHEN StatusName='Open' THEN 1 END) AS OpenTickets,
CONVERT(decimal(9,2),100.0*COUNT(CASE WHEN StatusName='Open' THEN 1 END)
/NULLIF(COUNT(*),0)) AS OpenPct
FROM #Tickets GROUP BY TeamID;Prefer Category Rows When the Domain Keeps Changing
A few stable measures fit conditional columns well. Dozens of frequently changing statuses can make the query difficult to maintain. A normal GROUP BY returning one row per category is then simpler and avoids rewriting a wide SELECT list every time the domain changes.
The following query reports the observed categories without hard-coding them into separate columns. The consuming report can display those rows directly or shape them according to its own contract. Preserve unknown or NULL categories deliberately rather than silently dropping them.
Conditional aggregation also does not guarantee a particular physical scan count. The optimizer chooses the access plan, and indexes can change the tradeoff compared with separate selective queries. Compare the actual plan and IO on representative data. The clear logical advantage is a single statement describing related measures over one population; the physical benefit still needs evidence from the workload.
SELECT TeamID,StatusName,COUNT_BIG(*) AS CategoryTickets
FROM #Tickets
GROUP BY TeamID,StatusName
ORDER BY TeamID,StatusName;Validate Conditional Aggregation as a Related Set
Test every status, NULL status, NULL due date, an exact due-time boundary, and an empty input. Confirm which counts can overlap and which should sum to the total. A sum that exceeds the total is valid for overlapping measures and wrong for an exclusive partition.
For SUM-based counts over an entirely empty input without GROUP BY, decide whether the report should convert NULL to zero. That display policy is separate from classifying rows. Keep integer overflow and percentage precision in mind for large populations.
Use conditional aggregation to make related dashboard measures clear and consistent. Choose the CASE result appropriate to COUNT or SUM, fix the observation rule, and expose the denominator. The query is useful when another person can read its category definitions and reproduce the same counts from the same input.
Related reading on this blog: Exploring PIVOT and UNPIVOT and Count Occurrences of a Specific Value in a Column.
A dashboard count is not just arithmetic, it is a category rule applied to a defined population.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
