Summing counts in a table

Question

We have the following query, performing as expected to, producing a single-column result with integers.

select count(ColumnA) - count(ColumnB)
from MyTable
where Occasion > convert(datetime2, concat(convert(varchar(7), Occasion, 126), ‘-01’))
group by convert(varchar(7), Occasion, 126)

Now, we'd like to get the aggregated sum of the values. When we tried sum(...), we got the error that aggregating isn't possible.

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

We tried summing directly like so:

select sum(count(ColumnA) - count(ColumnB)) ...

and tried summing over a whole select (like this):

select sum( select count(ColumnA) - count(ColumnB) ...)

getting "error near )", which was even more confusing.

How should we reason to get it under control?

I've found an answer relying on partitions but it seems way overhead for such a simple operation as ours. Is that a recommended approach?

Use a derived table or Common Table Express (CTE) to do your first aggregation (the `COUNT`s in this case) and then `SUM` that in the outer query. — Thom A, Oct 20 '22 at 08:58
I believe it's complaining about the CONVERT within the GROUP BY; so do that conversion within a CTE or subquery, then group by the derived column — RickyTillson, Oct 20 '22 at 09:00
The reason is that function takes an [expression](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/expressions-transact-sql?view=sql-server-ver16) as input, but a subquery (even scalar) is not an expression unless it is enclosed by parentheses. — astentx, Oct 20 '22 at 09:04

score 1 · Accepted Answer · answered Oct 20 '22 at 11:16

Is this something you're looking for:

WITH cte
AS
(
    SELECT COUNT(ColumnA) AS cntA, COUNT(ColumnB) AS cntB
    FROM MyTable
    WHERE Occasion > convert(datetime2, concat(convert(varchar(7), Occasion, 126), ‘-01’))
    GROUP BY convert(varchar(7), Occasion, 126)
    
)
SELECT SUM(cntA - cntB)
FROM cte

Summing counts in a table

1 Answers1