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?

Haemal

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

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

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. – Beatnik

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

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> – Beatnik

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. – Lange

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

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.

Yvetteyvon

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. – Haemal

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

@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? – Yvetteyvon

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

Briones

Answer 3

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.

Asperse

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

Answer 4

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

Folkestone

Answer 5

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.

Korte

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

Oshinski

Answer 7

fetch logs , scanLimitGBytes: -1, from:-1h
| filter matchesValue(k8s.namespace.name, "merch-item-prod") and matchesValue(k8s.container.name, "vendor-portal")
| filter contains(content, "Scheduler is already running")
| summarize count, alias:count

Guss

This code has nothing to do with SQL Server (question topic) – Beatnik

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Lor

Answer 8

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

Sabin

This just restates the question really. – Beatnik

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

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. – Beatnik

@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. – Haemal