Search notes:

Oracle SQL: select number of distinct values in a group

drop table tq84_t;

create table tq84_t (
   grp varchar2(10),
   v_1 number,
   v_2 number,
   v_3 number
);

insert into tq84_t values ('A',     1,    1,    0);
insert into tq84_t values ('A',     1,    1,    1);
insert into tq84_t values ('A',     1,    1,    1);
insert into tq84_t values ('A',     1,    1, null);

insert into tq84_t values ('B',     2,    1,    2);
insert into tq84_t values ('B',     2,    1,    2);
insert into tq84_t values ('B',     2,    0,    1);

insert into tq84_t values ('C',     3,    1, null);
insert into tq84_t values ('C',     3,    2,     3);
insert into tq84_t values ('C',     3,    3, null);
insert into tq84_t values ('C',     3,    4,    9);
insert into tq84_t values ('C',     3,    5, null);

insert into tq84_t values ('D',  null, null, null);
insert into tq84_t values ('D',  null, null, null);
insert into tq84_t values ('D',  null,    1, null);
insert into tq84_t values ('D',     1,    1, null);

select
   grp,
   v_1,
   v_2,
   count(*) over (partition by grp) cnt,
   count(*) over (partition by grp, v_1, v_2) cnt_distinct
from
   tq84_t
;


select
   grp,
   max(cnt_recs_in_group) cnt_recs_in_group,
   max(rank_values      ) cnt_distinct_values
from (
   select
      grp,
      count     (*) over (partition by grp                  ) cnt_recs_in_group,
      dense_rank( ) over (partition by grp order by v_1, v_2) rank_values
   from
      tq84_t
)
group by
   grp
; 

See also

dense_rank etc.

Index

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759428471, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/SQL/select/analytic/ranking/row_number-rank-dense_rank/distinct-values-in-group(89): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78