Display row where count(*) is zero?
来源:互联网 发布:ipad淘宝hd不能横屏 编辑:程序博客网 时间:2024/06/16 10:29
Display row where count(*) is zero?
Question: I need to write a SQL that counts all rows within a single table using an "in" list, and including all rows counts, even where the row count is zero. Here is my query:
select
sts_id,
count(*) mycount
from
task
where
sts_id in (1, 3, 4, 5, 6, 7, 8, 30, 39, 40, 41, 42)
group by sts_id;
sts_id mycount
------- -----------
1 48
8 1
39 16
42 76
As you see, I cannot display the rows where the count(*) is zero, and the in list seems to prevent the display of matching rows where the count(*) is zero. How do I get the rows with zero values to appear in my SQL output?
Answer: If you were doing this count with a table join, it’s easy to invoke an “outer join” to include missing rows, but here you have only a single table.
Because the "IN" list restricts which rows are selected, values with no row will be displayed unless we create them in a temporary table:
create table in_list (sts_id number);
insert into in_list values (1);
insert into in_list values (3);
. . . .
insert into in_list values (42);
Now, you can simple use this temporary table in an outer join to include the missing rows, something like this:
select
in_list.sts_id,
count(*) mycount
from
task
left outer join
in_list
group by sts_id;
Oracle guru Steve Callan offers this PL/SQL solution to displaying rows where count is zero. He notes that you would need to create a temporary create a table with just those values (and a column for the count) and modify the code to select sts_id from the new table, and update the new table with the count:
If the sts_id values do not exist in task, then create a table with just those values (and a column for the count) and modify the code to select sts_id from the new table, and update the new table with the count. Count will always return a number.
If you have duplicate sts_id values, then you need to account for them as well:
drop table task;
create table task (id number, line number);
insert into task values (1,1);
insert into task values (1,2);
insert into task values (2,null);
commit;
set serveroutput on
declare
v_cnt integer;
cursor c is
select distinct id from task;
begin
for r in c loop
select count(*) into v_cnt
from task
where id = r.id
and line > 0;
dbms_output.put_line(r.id||' '||v_cnt);
end loop;
end;
/
- Display row where count(*) is zero?
- How to display an empty row when the underlying dataset is empty
- Count zero segments in matrix
- The answer is zero.
- 70.You want to display only those product names with their list prices where the list price is at le
- Display row number in af:table
- display : table table-row rable-cell
- display: table-row; 实现等高布局
- where is my destination??
- Where is the TJH?
- Where is my love?
- OBJECT WHERE IS IT?
- Where is CodeCoolie?
- Where is my way ?
- Where is the Marble
- where is my dream!
- Where is the Marble?
- Where is The Marble
- 【转】JDK 版本 JAXB版本不一致 引发 java.lang.ClassNotFoundException:com.sun .xml.bind.v2.ContextFactory
- SSH远程会话管理工具 - screen使用教程
- 解决HierarchyViewer不能连接真机的问题
- Android Handler 机制
- Unity3d Android Sqlite 显示中文
- Display row where count(*) is zero?
- 浅谈sql中的in与not in,exists与not exists的区别
- 自己写通用表单验证插件
- IOS之地图和定位应用开发
- android 对象输出到文件
- 顶帖广告
- 用Installshield制作一个完整程序实例(一)--基本设置(一)
- win32 获取当前焦点的几个函数
- 我心里的程序员