Why can't I perform an aggregate function on a

问题:

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?

回答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.

回答2:

The gist of the issue is that there is no such concept as aggregate of an aggregate applied to a relation, see Aggregation. Having such a concept would leave too many holes in the definition and makes the GROUP BY clause impossible to express: it needs to define both the inner aggregate GROUP BY clause and the outer aggregate as well! This applies also to the other aggregate attributes, like the HAVING clause.

However, the result of an aggregate applied to a relation is another relation, and this result relation in turn can support a new aggregate operator. This explains why you can aggregate the result into an outer SELECT. This leaves no ambiguity in the definition, each SELECT has its own distinct GROUP BY/HAVING clauses.

回答3:

In simple terms, aggregation functions operate over a column and generate a scalar value, hence they cannot be applied over their result. When you create a select statement over a scalar value you transform it into an artificial column, that's why it can be used by an aggregation function again.

Please note that most of the times there's no point in applying an aggregation function over the result of another aggregation function: in your sample sum(count(id)) == count(id).

回答4:

i would like to know what your expected result in this sql

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

when you use the count function, only 1 result(total count) will be return. So, may i ask why you want to sum the only 1 result.

You will surely got the error because an aggregate function cannot perform on an expression containing an aggregate or a subquery.

回答5:

It's working for me using SQLFiddle, not sure why it would't work for you. But I do have an explanation as to why it might not be working for you and why the alternative would work...

Your example is using a keyword as a column name, that may not always work. But when the column is only in a sub expression, the query engine is free to discard the name (in fact it probaly does) so the fact that it potentially potentially conflicts with a key word may be disregarded.

EDIT: in response to your edit/comment. No, the two aren't equivalent. The RESULT would be equivalent, but the process of getting to that result is not at all similar. For the first to work, the parser has do some work that simply doesn't make sense for it to do (applying an aggregate to a single value, either on a row by row basis or as), in the second case, an aggregate is applied to a table. The fact that the table is a temporary virtual table will be unimportant to the aggregate function.

回答6:

I think you can write the sql query, which produces 'count' of rows for the required output. Functions do not take aggregated functions like 'sum' or aggregated subquery. My problem was resolved by using a simple sql query to get the count out....

回答7:

Microsoft SQL Server doesn’t support it.

You can get around this problem by using a Derived table:

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

---

On the other hand using the below code will give you an error message.

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

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