Search notes:

Oracle SQL: DISTINCT and COUNT

drop   table tq84_abc purge;
create table tq84_abc (
  gr  varchar2(5),
  v1  number
);

insert into tq84_abc values ('AAA', 5);
insert into tq84_abc values ('AAA', 5);

insert into tq84_abc values ('AAA', 7);

insert into tq84_abc values ('AAA', 9);
insert into tq84_abc values ('AAA', 9);

---------------------------------------

insert into tq84_abc values ('ZZZ', 6);
insert into tq84_abc values ('ZZZ', 6);

insert into tq84_abc values ('ZZZ', 7);

insert into tq84_abc values ('ZZZ', 8);

select
  count(distinct gr),
  count(distinct v1),
  avg  (distinct v1)
from
  tq84_abc;
--
-- COUNT(DISTINCTGR) COUNT(DISTINCTV1) AVG(DISTINCTV1)
-- ----------------- ----------------- ---------------
--
                2                 5               7

select
  gr,
  count(distinct v1),
  avg  (distinct v1)
from
  tq84_abc
group by
  gr;

-- GR    COUNT(DISTINCTV1) AVG(DISTINCTV1)
-- ----- ----------------- ---------------
-- AAA                   3               7
-- ZZZ                   3               7
Github repository Oracle-Patterns, path: /SQL/select/aggregate/distinct.sql

NULL is no distinct value

count distinct(…) doesn't consider null to be a distinct value: 
create table tq84_count_distinct_test ( a number );
 
begin
   insert into tq84_count_distinct_test values ( 1 );
   insert into tq84_count_distinct_test values ( 1 );
   insert into tq84_count_distinct_test values ( 1 );
 
   insert into tq84_count_distinct_test values ( 2 );
 
   insert into tq84_count_distinct_test values ( 3 );
   insert into tq84_count_distinct_test values ( 3 );
   insert into tq84_count_distinct_test values ( 3 );
   insert into tq84_count_distinct_test values ( 3 );
 
   commit;
end;
/
The following statement returns 3 (which corresponds to the number distinct values in tq84_count_distinct_test): 
select
   count(distinct a) cnt_dist
from
   tq84_count_distinct_test;
A null value is inserted - but the select statement will still return 3: 
insert into tq84_count_distinct_test values ( null );
commit;
Using sys_op_map_nonnull gives the alternative (some would say: more accurate) value 4: 
select
   count(distinct sys_op_map_nonnull(a)) cnt_dist
from
   tq84_count_distinct_test;

COUNT(DISTINCT col-1, col_2) raises ORA-00909

Unlike some SQL implementations found in other databases (for example MySQL), Oracle does not allow to use a (comma-separated) list of expressions in the count(distinct …) clause. The following statement raises ORA-00909: invalid number of arguments: 
select count(distinct owner, object_type) from all_objects
This shortcoming is typically circumvented with a subquery: 
select count(*) from (select distinct owner, object_type from all_objects);

See also

The approx_count_distinct(…) function.
aggregate function

Index