Hi,
How do I count(*) over a group that is less aggregated than the group by (in Oracle 12.1)?
Example:
select deptno, lastname, count(*)
, count(*) keep (dense_rank last order by count(*)) over (partition by deptno) ?????
from emp join emp on ...
group by deptno, lastname
I know this works as well:
with x as ( select deptno, lastname, count(*) total from emp join emp on ... group by deptno, lastname)
select y.deptno, y.lastname, y.total, z.total
from x y join (select deptno, sum(total) from x) z on y.deptno = z.deptno