sql - Why can't I perform an aggregate function on an expression containing an aggregate but I can do so by creating a new select statement around it?

Question

Why is it that in SQL Server I can't do this:

select  sum(count(id)) as 'count'
from    table

But I can do

select sum(x.count)
from
(
    select  count(id) as 'count'
    from    table   
) x

Are they not essentially the same thing? How am I meant to be thinking about this in order to understand why the first block of code isn't allowed?

Answer 1

SUM() in your example is a no-op - SUM() of a COUNT() means the same as just COUNT(). So neither of your example queries appear to do anything useful.

It seems to me that nesting aggregates would only make sense if you wanted to apply two different aggregations - meaning GROUP BY on different sets of columns. To specify two different aggregations you would need to use the GROUPING SETS feature or SUM() OVER feature. Maybe if you explain what you want to achieve someone could show you how.