Counting the Not-Directly-Countable in T-SQL

A Practical Pattern for Trustworthy Distinct Counts, Subtotals, and Grand Totals

Not a member? Click here to read.

Photo by Dong Xie on Unsplash

Why This Post Exists

One of my favorite college courses was Combinatorics, a graduate level course about counting things. For the past 20 years, when people asked me what do I do, my answer was I count things. Over the years, T-SQL has evolved its counting capabilities. I realized I was stuck in 2010 T-SQL, so I thought it was time I learned some of the new ways.

Introduction

Some counts are easy: “How many work items did we complete?”

That is a normal aggregate.

[Work Item Count] = COUNT_BIG(*)

But some counts are not that simple: “How many clients did we service by work country, work type, and division?”

That sounds like a normal reporting question. It is not.

The problem appears when we need subtotals and grand totals. A client may appear in more than one country, more than one work type, or more than one division. If we count distinct clients at the lowest level and then sum those counts later, we may overcount.

That is the trap: Distinct counts are not reliably additive.

A subtotal is not always the sum of its child rows. Sometimes the subtotal has to be recalculated at its own level.

This is one of those reporting problems where the SQL can be technically correct at the detail level, while the report is still wrong.

The data is not lying. The aggregation path is.

The Scenario

Assume we have two large tables:

[work item]
[client]

A work item has attributes like:

[client guid]
[division]
[work type]
[work country]
[year]

A client has attributes like:

[client guid]
[home country]

The business question is: Per work country, how many clients did we service by work type and division? We also need the answer to work in a reporting tool that cannot load all source-level rows because the underlying data is too large.

So we need to precompute:

• Detail rows
• Subtotals
• Grand totals
• Trustworthy distinct client counts
• Labels that make sense to report consumers
• Metadata that prevents accidental misuse

This is not only a performance problem. It is a meaning-preservation problem.

The Wrong Pattern

The tempting query is this:

SELECT
    [Work Country] = [wi].[work country],
    [Work Type]    = [wi].[work type],
    [Division]     = [wi].[division],
    [Client Count] = COUNT(DISTINCT [wi].[client guid])
FROM [dbo].[work item] AS [wi]
GROUP BY
    [wi].[work country],
    [wi].[work type],
    [wi].[division];

This query is not wrong for the detail rows. But it becomes dangerous if the reporting tool later does this:

SUM([Client Count])

That sum may be false.

For example:

|Country | Type       | Division | Distinct Clients |
|--------|------------|----------|------------------|
| Canada | Inspection | East     | 100              |
| Canada | Inspection | West     | 80               |

If 25 clients had both East and West work, then the true Canada / Inspection subtotal is not 180.

It is 155.

100 + 80 - 25 = 155

The reporting tool cannot discover that overlap from the aggregated rows alone. The detail needed to deduplicate the clients is already gone. Once we compress the data, some truths become unrecoverable.

That is why distinct-count subtotals must be calculated from the source-level relationship, not from already-aggregated rows.

Yesterday’s Better Patterns

Over the years I have learned several different ways to deal with these challenges. Until recently, I relied on GROUP BY WITH ROLLUP.

While that still works, I thought it was time that I learned the newer patterns.

Today’s Better Pattern

The safer pattern is to calculate each required reporting level directly.

This is where GROUPING SETS helps.

GROUPING SETS lets us return multiple aggregation levels from one query. More importantly, each subtotal is calculated from the underlying source rows, not by summing child rows.

WITH [Base] AS
(
    SELECT
        [Client GUID]  = [wi].[client guid],
        [Work Country] = [wi].[work country],
        [Work Type]    = [wi].[work type],
        [Division]     = [wi].[division]
    FROM [dbo].[work item] AS [wi]
    INNER JOIN [dbo].[client] AS [c]
        ON [c].[client guid] = [wi].[client guid]
)
SELECT
    [Work Country] = [Work Country],
    [Work Type]    = [Work Type],
    [Division]     = [Division],

    [Is Work Country Total] = GROUPING([Work Country]),
    [Is Work Type Total]    = GROUPING([Work Type]),
    [Is Division Total]     = GROUPING([Division]),

    [Grouping ID] = GROUPING_ID
    (
        [Work Country],
        [Work Type],
        [Division]
    ),

    [Work Item Count] = COUNT_BIG(*),
    [Client Count]    = COUNT(DISTINCT [Client GUID])
FROM [Base]
GROUP BY GROUPING SETS
(
    ([Work Country], [Work Type], [Division]), -- detail
    ([Work Country], [Work Type]),             -- all divisions
    ([Work Country], [Division]),              -- all work types
    ([Work Type], [Division]),                 -- all work countries
    ([Work Country]),                          -- country total
    ([Work Type]),                             -- work type total
    ([Division]),                              -- division total
    ()                                         -- grand total
);

This query takes care of returning subtotals (re)calculated independently at each level.

Why GROUPING() Matters

Subtotal rows often use NULL as a placeholder for the columns that are being rolled up.

But that creates a problem: A real missing value and a subtotal placeholder can both look like NULL. Those are very different meanings.

• [Division] IS NULL because the source value is missing — we do not know the division.
• [Division] IS NULL because the row is a subtotal — this row represents all divisions.

That difference must be visible in the output.

The GROUPING() function tells us whether a column has been rolled up. A value of 1 means the column is part of a subtotal. A value of 0 means the row still has a normal grouping value for that column.

So instead of letting the report guess what NULL means, we tell it.

Make the Output Report-Friendly

Raw SQL output is not always safe reporting output. For reporting, we should include:

• The raw dimensional values
• The subtotal flags
• Display labels
• An aggregation level
• A warning that distinct counts should not be summed

WITH [Base] AS
(
    SELECT
        [Client GUID]  = [wi].[client guid],
        [Work Country] = [wi].[work country],
        [Work Type]    = [wi].[work type],
        [Division]     = [wi].[division],
        [Year]         = [wi].[year],
        [Home Country] = [c].[home country]
    FROM [dbo].[work item] AS [wi]
    INNER JOIN [dbo].[client] AS [c]
        ON [c].[client guid] = [wi].[client guid]
),
[Aggregated] AS
(
    SELECT
        [Work Country] = [Work Country],
        [Work Type]    = [Work Type],
        [Division]     = [Division],

        [Is Work Country Total] = GROUPING([Work Country]),
        [Is Work Type Total]    = GROUPING([Work Type]),
        [Is Division Total]     = GROUPING([Division]),

        [Grouping ID] = GROUPING_ID
        (
            [Work Country],
            [Work Type],
            [Division]
        ),

        [Work Item Count] = COUNT_BIG(*),
        [Client Count]    = COUNT(DISTINCT [Client GUID])
    FROM [Base]
    GROUP BY GROUPING SETS
    (
        ([Work Country], [Work Type], [Division]),
        ([Work Country], [Work Type]),
        ([Work Country], [Division]),
        ([Work Type], [Division]),
        ([Work Country]),
        ([Work Type]),
        ([Division]),
        ()
    )
)
SELECT
    [Work Country] = [Work Country],
    [Work Type]    = [Work Type],
    [Division]     = [Division],

    [Work Country Label] =
        CASE
            WHEN [Is Work Country Total] = 1 THEN '(All Work Countries)'
            WHEN [Work Country] IS NULL THEN '(Missing Work Country)'
            ELSE [Work Country]
        END,

    [Work Type Label] =
        CASE
            WHEN [Is Work Type Total] = 1 THEN '(All Work Types)'
            WHEN [Work Type] IS NULL THEN '(Missing Work Type)'
            ELSE [Work Type]
        END,

    [Division Label] =
        CASE
            WHEN [Is Division Total] = 1 THEN '(All Divisions)'
            WHEN [Division] IS NULL THEN '(Missing Division)'
            ELSE [Division]
        END,

    [Is Work Country Total],
    [Is Work Type Total],
    [Is Division Total],
    [Grouping ID],

    [Aggregation Level] =
        CASE
            WHEN [Is Work Country Total] = 0
             AND [Is Work Type Total] = 0
             AND [Is Division Total] = 0
                THEN 'Detail'

            WHEN [Is Work Country Total] = 1
             AND [Is Work Type Total] = 1
             AND [Is Division Total] = 1
                THEN 'Grand Total'

            ELSE 'Subtotal'
        END,

    [Work Item Count],
    [Client Count],

    [Client Count Is Additive] = CONVERT(bit, 0),

    [Client Count Additivity Warning] =
        'Do not sum this value across rows. Distinct client counts must be recalculated at each aggregation level.'
FROM [Aggregated];

This is not just SQL hygiene. It is reporting ethics. We are preventing the report from whispering false certainty.

What the Reporting Output Should Look Like

A trustworthy reporting table should make subtotal rows obvious. Here is a simplified example:

[embed]

The key point is visible in the Canada / Inspection rows:

• Canada / Inspection / East: 100 distinct clients had Canadian inspection work in the East division.
• Canada / Inspection / West: 80 distinct clients had Canadian inspection work in the West division.
• Canada / Inspection / All Divisions: 155 distinct clients had Canadian inspection work in any division.
• 100 + 80 = 180: Not trustworthy as a distinct client subtotal because some clients may appear in both divisions.
• 155: Trustworthy because it was recalculated directly from the source-level relationship.

Note:

• The subtotal is not a sum of the visible rows.
• The subtotal is its own calculation.
• That distinction is the whole game.

Why Work Item Count and Client Count Behave Differently

These two measures look similar, but they do not behave the same way.

• Work Item Count: Number of work item rows is additive.
• Client Count: Number of distinct clients serviced is not additive.

COUNT_BIG(*) gives us the number of source rows in each group.

COUNT(DISTINCT [Client GUID]) gives us the number of unique clients in the current group.

The phrase “current group” is doing a lot of work there. If the current group is Canada / Inspection / East, we get one number.

If the current group is Canada / Inspection / All Divisions, we get a different number. Both numbers can be correct. They are answering different questions.

That gives us a practical reporting rule:

Work item counts can often be summed. Distinct client counts usually should not be summed unless the grouping guarantees no overlap.

• The data model decides whether summing is safe.
• The visual does not get to decide that after the fact.

Materializing the Aggregate

For large datasets, we usually do not want to run this aggregation live inside the reporting tool.

Instead, we can materialize the results into a reporting table.

DROP TABLE IF EXISTS [reporting].[work item client count aggregate];

CREATE TABLE [reporting].[work item client count aggregate]
(
    [Work Country]                      nvarchar(100) NULL,
    [Work Type]                         nvarchar(100) NULL,
    [Division]                          nvarchar(100) NULL,

    [Work Country Label]                nvarchar(120) NOT NULL,
    [Work Type Label]                   nvarchar(120) NOT NULL,
    [Division Label]                    nvarchar(120) NOT NULL,

    [Is Work Country Total]             bit NOT NULL,
    [Is Work Type Total]                bit NOT NULL,
    [Is Division Total]                 bit NOT NULL,

    [Grouping ID]                       int NOT NULL,
    [Aggregation Level]                 varchar(20) NOT NULL,

    [Work Item Count]                   bigint NOT NULL,
    [Client Count]                      int NOT NULL,
    [Client Count Is Additive]          bit NOT NULL,

    [Client Count Additivity Warning]   varchar(200) NOT NULL,
    [Refresh UTC Datetime]              datetime2(3) NOT NULL
);

Then load it as part of the refresh process.

INSERT INTO [reporting].[work item client count aggregate]
(
    [Work Country],
    [Work Type],
    [Division],
    [Work Country Label],
    [Work Type Label],
    [Division Label],
    [Is Work Country Total],
    [Is Work Type Total],
    [Is Division Total],
    [Grouping ID],
    [Aggregation Level],
   [Work Item Count],
    [Client Count],
    [Client Count Is Additive],
    [Client Count Additivity Warning],
    [Refresh UTC Datetime]
)
SELECT
    [Work Country],
    [Work Type],
    [Division], 
    [Work Country Label],
    [Work Type Label],
    [Division Label],
    [Is Work Country Total],
    [Is Work Type Total],
    [Is Division Total],
    [Grouping ID],
    [Aggregation Level],
    [Work Item Count],
    [Client Count],
    [Client Count Is Additive],
    [Client Count Additivity Warning],
    [Refresh UTC Datetime] = SYSUTCDATETIME()
FROM
(
    -- Put the final SELECT query from the previous section here.
) AS [x];

In a production data mart or warehouse, consider adding:

[Refresh Batch GUID]
[Source Row Count]
[Metric Name]
[Metric Definition]
[Grain Description]
[Data Quality Status]

Because the real question is not only: What did we count?

It is also: Can someone looking at this report understand what the count means without accidentally misusing it?

That is the trust layer.

Recommended Report Rules

The report should not simply show a matrix of numbers. It should help the reader understand the kind of row they are looking at.

Recommended Display Columns

• Work Country Label: Shows actual value, missing value, or all-values subtotal.
• Work Type Label: Shows actual value, missing value, or all-values subtotal.
• Division Label: Shows actual value, missing value, or all-values subtotal.
• Aggregation Level: Identifies Detail, Subtotal, or Grand Total.
• Work Item Count: Additive operational volume.
• Client Count: Distinct clients at this exact aggregation level.
• Client Count Is Additive: Usually false for distinct client counts.
• Refresh UTC Datetime: Shows data freshness.

Recommended Visual Behavior

For [Client Count]:

• Do not allow default summing.
• Use the precomputed subtotal rows.
• Make subtotal rows visually distinct.
• Show (All Divisions) instead of a blank value.
• Show (Missing Division) separately from (All Divisions).
• Add a tooltip explaining that distinct counts are calculated at the displayed aggregation level.

Recommended Footnote

Client Count is a distinct count calculated at the displayed aggregation level. Subtotals and grand totals are precomputed from source-level work item/client relationships. They may not equal the sum of visible child rows because the same client can appear in multiple countries, work types, or divisions.

A Small Data Quality Check

Here is a simple diagnostic query to look for cases where a subtotal differs from the sum of its children.

This query returns rows when the child-row sum is not equal to the independently calculated subtotal. That difference is not necessarily an error. In fact, for distinct client counts, it may be expected. But it is a useful teaching and validation query.

WITH [Detail] AS
(
    SELECT
        [Work Country] = [wi].[work country],
        [Work Type]    = [wi].[work type],
        [Division]     = [wi].[division],
        [Client Count] = COUNT(DISTINCT [wi].[client guid])
    FROM [dbo].[work item] AS [wi]
    GROUP BY
        [wi].[work country],
        [wi].[work type],
        [wi].[division]
),
[Child Sum] AS
(
    SELECT
        [Work Country],
        [Work Type],
        [Child Row Client Count Sum] = SUM([Client Count])
    FROM [Detail]
    GROUP BY
        [Work Country],
        [Work Type]
),
[True Subtotal] AS
(
    SELECT
        [Work Country] = [wi].[work country],
        [Work Type]    = [wi].[work type],
        [Client Count] = COUNT(DISTINCT [wi].[client guid])
    FROM [dbo].[work item] AS [wi]
    GROUP BY
        [wi].[work country],
        [wi].[work type]
)
SELECT
    [cs].[Work Country],
    [cs].[Work Type],
    [Child Row Client Count Sum] = [cs].[Child Row Client Count Sum],
    [True Subtotal Client Count] = [ts].[Client Count],
    [Overcount From Summing Children] =
        [cs].[Child Row Client Count Sum] - [ts].[Client Count]
FROM [Child Sum] AS [cs]
INNER JOIN [True Subtotal] AS [ts]
    ON [ts].[Work Country] IS NOT DISTINCT FROM [cs].[Work Country]
   AND [ts].[Work Type]    IS NOT DISTINCT FROM [cs].[Work Type]
WHERE [cs].[Child Row Client Count Sum] <> [ts].[Client Count]
ORDER BY
    [Overcount From Summing Children] DESC,
    [cs].[Work Country],
    [cs].[Work Type];

This is a useful query to show someone who thinks the reporting tool can “just sum the rows.”

Sometimes it can. Sometimes it can’t. This query shows where the line is.

The Core Lesson

This is not really about GROUPING SETS. It is about preserving meaning when we look at the big picture. When we move from millions of source rows to a small reporting table, we lose detail. Once that detail is gone, some questions cannot be answered safely.

So the safer pattern is:

1. Start from the lowest useful source relationship.
2. Calculate each required aggregation level directly.
3. Store subtotal flags, not just subtotal labels.
4. Separate real missing values from “all values.”
5. Mark non-additive measures clearly.
6. Prevent the visualization layer from inventing totals from incomplete context.
7. Add footnotes and metadata that explain what the numbers mean.

The data warehouse should not merely make reports faster. It should make the truth harder to accidentally damage. That is the whole point.