英文:
Print zero for blank/empty result in oarcle sql query
问题
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
英文:
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
答案1
得分: 2
If you want the values as columns (not rows) then you only need to SELECT from the table once and aggregate twice (using conditional aggregation to find the U values):
如果您希望将值显示为列（而不是行），则只需从表格中选择一次并进行两次汇总（使用条件聚合来查找“U”值）：
```lang-sql
SELECT COUNT(COUNT(DISTINCT id)) AS t,
COUNT(CASE WHEN COUNT(DISTINCT id) > 1 THEN 1 END) AS u
FROM TEMP
GROUP BY CAT;
Which outputs:
这将产生以下结果：
| T | U |
|---|---|
| 6 | 0 |
If you do want it as rows then you can UNPIVOT (which, again, does not use UNION and only SELECTs from the table once):
如果您希望将其显示为行，则可以使用 UNPIVOT（再次强调，不使用 UNION，只需从表格中选择一次）：
SELECT *
FROM (
SELECT COUNT(COUNT(DISTINCT id)) AS t,
COUNT(CASE WHEN COUNT(DISTINCT id) > 1 THEN 1 END) AS u
FROM TEMP
GROUP BY CAT
)
UNPIVOT (cnt FOR total_cnt IN (t, u));
Which outputs:
这将产生以下结果：
| TOTAL_CNT | CNT |
|---|---|
| T | 6 |
| U | 0 |
英文:
If you want the values as columns (not rows) then you only need to SELECT from the table once and aggregate twice (using conditional aggregation to find the U values):
SELECT COUNT(COUNT(DISTINCT id)) AS t,
COUNT(CASE WHEN COUNT(DISTINCT id) > 1 THEN 1 END) AS u
FROM TEMP
GROUP BY CAT;
Which outputs:
| T | U |
|---|---|
| 6 | 0 |
If you do want it as rows then you can UNPIVOT (which, again, does not use UNION and only SELECTs from the table once):
SELECT *
FROM (
SELECT COUNT(COUNT(DISTINCT id)) AS t,
COUNT(CASE WHEN COUNT(DISTINCT id) > 1 THEN 1 END) AS u
FROM TEMP
GROUP BY CAT
)
UNPIVOT (cnt FOR total_cnt IN (t, u));
Which outputs:
| TOTAL_CNT | CNT |
|---|---|
| T | 6 |
| U | 0 |
答案2
得分: 0
这是一种选项：
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>
英文:
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>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论