Some databases, e.g. Oracle, allow TWO levels of nesting:
SELECT avg(sal) FROM emp GROUP BY deptno;
AVG(SAL)
========
1,566.67
2,175.00
2,916.65
SELECT avg(avg(sal)) FROM emp GROUP BY deptno;
AVG(SUM(SAL))
=============
9,675
The first level aggregates by department (returning 3 records), and the second
level computes the grand total (returning 1 record). But that is an exceptional
case.
Generally, any expression in the SELECT or HAVING clause of an aggregate query
is either ‘before’ or ‘after’ aggregation. Consider
SELECT t.x + 1 AS a, 2 + SUM(t.y + 3) AS b
FROM t
GROUP BY t.x
The expressions “t.y” and “t.y + 3” occur before aggregation; “t.x”, “t.x + 1”,
“SUM(t.y + 3)” and “2 + SUM(t.y + 3)” occur after aggregation. SQL semantics
rely heavily on this stratification. Allowing an extra level of aggregation
would mess it all up.
Julian
> On Feb 10, 2022, at 9:45 AM, Justin Swanhart <greenl...@gmail.com> wrote:
>
> This is a SQL limitation.
>
> mysql> select sum(1);
> +--------+
> | sum(1) |
> +--------+
> | 1 |
> +--------+
> 1 row in set (0.00 sec)
>
> mysql> select sum(sum(1));
> ERROR 1111 (HY000): Invalid use of group function
>
> On Thu, Feb 10, 2022 at 12:39 PM Gavin Ray <ray.gavi...@gmail.com> wrote:
>
>> Went to test this query out and found that it can't be performed:
>>
>> SELECT
>> JSON_OBJECT(
>> KEY 'users'
>> VALUE JSON_ARRAYAGG(
>> JSON_OBJECT(
>> KEY 'name' VALUE "users"."name",
>> KEY 'todos' VALUE JSON_ARRAYAGG(
>> JSON_OBJECT(
>> KEY 'description' VALUE "todos"."description"
>> )
>> )
>> )
>> )
>> )
>> FROM
>> "users"
>> LEFT OUTER JOIN
>> "todos" ON "users"."id" = "todos"."user_id";
>>
>> Checking the source, seems this is a blanket policy, not a
>> datasource-specific thing.
>> From a functional perspective, it doesn't feel like it's much different
>> from JOINs
>> But I don't understand relational theory or DB functionality in the least,
>> so I'm not fit to judge.
>>
>> Just curious why Calcite doesn't allow this
>>
