That is really neat about Oracle. The alternative in general is to use a subquery: SELECT avg(avg(sal)) FROM emp GROUP BY deptno; becomes select avg(the_avg) from (select avg(sal) from emp group b deptno) an_alias;
or with the_cte as (select avg(sal) x from emp group by deptno) select avg(x) from the_cte; On Thu, Feb 10, 2022 at 3:03 PM Julian Hyde <jhyde.apa...@gmail.com> wrote: > 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 > >> > >
