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
;