Menu
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?
345
We cannot use the WHERE clause with aggregate functions because it works for filtering individual rows. In contrast, HAVING can works with aggregate functions because it is used to filter groups.
The HAVING clause includes one or more conditions that should be TRUE for groups of records. It is like the WHERE clause of the GROUP BY clause. The only difference is that the WHERE clause cannot be used with aggregate functions, whereas the HAVING clause can use aggregate functions.
Aggregate functions can be used only in the select list or in a having clause. They cannot be used in a where or group by clause. Aggregate functions are of two types. Aggregates applied to all the qualifying rows in a table (producing a single value for the whole table per function) are called scalar aggregates.
An aggregate function can be used in a WHERE clause only if that clause is part of a subquery of a HAVING clause and the column name specified in the expression is a correlated reference to a group. If the expression includes more than one column name, each column name must be a correlated reference to the same group.
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.
93
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
