I have this odd case where I'd like to display all list values with i.e. counter My test table content
name, misc
A, blah
B, 123
A, asd
now, this query will fetch results just for A and B, but how can I display C "results"
select name, count(*) from table
where name in ('A','B','C')
result I'd like to get
A 2
B 1
C 0
this case is driving me mad for a day already. please help
Cheers, Dan
I think you'll have to use an outer join for this type of query, such as:
WITH mylist AS (SELECT 'A' name FROM DUAL
UNION ALL SELECT 'B' FROM DUAL
UNION ALL SELECT 'C' FROM DUAL)
SELECT l.name, COUNT(t.name)
FROM mylist l
LEFT JOIN mytable t ON l.name = t.name
GROUP BY l.name
You could use a table object instead of a UNION ALL:
CREATE TYPE name_list_t AS TABLE OF VARCHAR2(30);
/
SELECT l.name, COUNT(t.name)
FROM (SELECT column_value name FROM TABLE(name_list_t('A', 'B', 'C')) l
LEFT JOIN mytable t ON l.name = t.name
GROUP BY l.name
You can accomplish this by outer joining a table containing your list of values:
WITH lookup AS (
SELECT decode(LEVEL
,1,'A'
,2,'B'
,3,'C') col1
FROM dual
CONNECT BY LEVEL <= 3
)
,test AS (
SELECT decode(LEVEL
,1,'A'
,2,'A'
,3,'B') col1
,decode(LEVEL
,1,'blah'
,2,'123'
,3,'asd') col2
FROM dual
CONNECT BY LEVEL <= 3
)
SELECT lookup.col1,count(test.col1)
FROM lookup
,test
WHERE lookup.col1 = test.col1 (+)
GROUP BY lookup.col1
;
Returns:
A 2
B 1
C 0
In my example "lookup" is generated on the fly. You can either use this approach or most probably better use your own list of values table.
get a splitter function from here: how to convert csv to table in oracle
and then
select a.column_value, count(t.name)
from table(splitter('A,B,C')) a
left join table t on (t.name = a.column_value)
group by a.column_value
