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?

Solution 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.

Solution 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.

Solution 3

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.

Solution 4

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.

Solution 5

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).

View more solutions

Comments

[Sergey Kalinichenko]

The first way should work too. What's the error you're getting?

[Rakesh Juyal]

@Michael care to share the error you are getting when you run first sql. It should run fine.

[Martin Smith]

A Scalar Aggregate (No GROUP BY) always returns exactly one row. Under what circumstances would it make sense to apply an aggregate to that one row? A vector aggregate returns one row per group. Whilst sometimes it might be useful to apply another aggregation to the result of that you would need a different GROUP BY applied for the two aggregates for this to be useful.

[Martin Smith]

Although having said that it looks like Sybase allows this and just implicitly assumes that the outer aggregate is to be a scalar one

[Martin Smith]

The SQL-92 Standard explicitly prohibits nesting aggregates The <value expression> simply contained in <set function specification> shall not contain a <set function specification> or a <subquery>

[Andriy M]

Put simply, aggregate(aggregate(...)) is impossible because there's no way to specify GROUP BY ... OVER (GROUP BY ...). I.e. if we wanted to nest aggregates, we should also be able to nest GROUP BYs, which we have not been allowed (yet?). You say there are no GROUP BYs in your examples? But there are, namely GROUP BY (), which is always implied when not specified explicitly.

[david blaine]

Why do i get an error when i do only - ( select count(id) as 'count' from table ) x ?

[Michael A]

Apologies, looks like I made a mistake in the original (first) query. Now corrected!

[Martin Smith]

This just restates the question really.

[Nilish]

@MartinSmith Nope, It's a fact that Issue can be resolved using Derived table.

[Martin Smith]

A fact which is already stated in the question. The second example shows a derived table works. The question is Why is this the case.

[Michael A]

I'm more curious as to why I have to use a derived table to accomplish this - Why doesn't SQL Server support my first example but it does my second? They seem essentially the same to me. No real purpose, asking for educations sake.

[Michael A]

@PankajGarg Martin is correct. I know it can't be done, I want to know why. You just re-stated my question in a different way (not very different at all really) and posed it as an answer. It isn't one.

[aF.]

@Michael SQL code doesn't allow SUM() with agregate functions. If it doesn't allow, it can't be used :P

[nvogel]

@Michael, Because there would be no way in a single SELECT statement to specify different aggregations for the nested functions and there would be no point if both functions used the same aggregation. Does that answer the question?