Summing counts in a table-CodePudding

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?

CodePudding user response：

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