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;
/
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
