COUNT(*) KEEP (DENSE_RANK LAST ORDER BY COUNT(*)) OVER (PARTITION BY A,B)

Martin J.

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

Martin J.

I found the answer myself in an ultimate attempt to fix this. It seems the following is the answer:

sum(count*)) keep ...

Martin J.

I found the answer myself in an ultimate attempt to fix this. It seems the following is the answer:

sum(count*)) keep ...