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 COUNTs in this case) and then SUM that in the outer query. — Thom A
– Thom A ♦, Commented Oct 20, 2022 at 8: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
– RickyTillson, Commented Oct 20, 2022 at 9:00
The reason is that function takes an expression as input, but a subquery (even scalar) is not an expression unless it is enclosed by parentheses. — astentx
– astentx, Commented Oct 20, 2022 at 9:04

TheMah · Accepted Answer · 2022-10-20 11:16:29Z

1

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

answered Oct 20, 2022 at 11:16

TheMah

5018 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Summing counts in a table

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related