Print zero for blank/empty result in oarcle sql query

Question

I want to display 0 when query returns no records . I tried with nvl but its not working . please advise .

create table  temp (id number , cat varchar2(10));
insert into temp values (100,'EUR');
insert into temp values (101,'MAR');
insert into temp values (102,'AST');
insert into temp values (103,'GHK');
insert into temp values (104,'MST');
insert into temp values (105,'LAL');


select TOTAL_CNT , COUNT(*) 
FROM
(
SELECT 'T' TOTAL_CNT, nvl(count(DISTINCT id),0) cnt FROM TEMP GROUP BY CAT
union all
SELECT  'U' UNIQUE_CNT ,nvl(count(DISTINCT id),0) cnt FROM TEMP GROUP BY CAT HAVING  COUNT(DISTINCT ID)>1
) GROUP BY TOTAL_CNT;

expected output : I am not getting "U" values.

TOTAL_CNT    COUNT(*) 
       T          6
       U          0
    

Answer 1

This is one option:

SQL> with
  2  t_unique as
  3    (select count(distinct id) cnt
  4     from temp
  5     group by cat
  6     having count(distinct id) > 1
  7    ),
  8  t_total as
  9    (select count(distinct id) cnt
 10     from temp
 11     group by cat
 12    )
 13  select 'T' what, (select count(*) from t_total ) cnt from dual
 14  union
 15  select 'U' what, (select count(*) from t_unique) cnt from dual;

WHAT         CNT
----- ----------
T              6
U              0

SQL>