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

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);

Oracle SQL: DISTINCT and COUNT

NULL is no distinct value

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

See also