COUNT(*) KEEP (DENSE_RANK LAST ORDER BY COUNT(*)) OVER (PARTITION BY A,B) — oracle-tech

    Forum Stats

  • 3,714,815 Users
  • 2,242,633 Discussions
  • 7,845,076 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

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

Martin J.
Martin J. Member Posts: 9 Green Ribbon
edited January 11 in SQL & PL/SQL

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

Tagged:

Best Answer

Answers

Sign In or Register to comment.