ORACLE display count to zero, when row is null in column

Question

I have a table with many of columns, I count the specific names on column and if that name not on list, I want to display it zero, but not get in to list.

SELECT Names, COUNT (*)
FROM NAMESTABLE
WHERE names IN
                ('Jenny',
                 'Penny',
                 'George',
                 'James',
                 'Jessica',
                 'Monica',
                 'Erica')
             AND  
adeddate BETWEEN '2014/10/15' AND '2014/10/16' 
GROUP BY names 

---

NAMES  ||   COUNT(*)
Jenny  ||   33
Penny  ||   4
George ||   25
James  ||   87

so i want to Jessica, Monica, Erica as ZERO even these names are not in COLUMN

Names    ||  Count(*)
Jenny    ||    33
Penny    ||     4
George   ||    25 
James    ||    87
Jessica  ||     0
Monica   ||     0
Erica    ||     0

nvl(count(*),0) does not work

Solution

You could group your results by name and then left join the result to a table of your names to fill in the blanks:

SELECT    rn.name, NVL(cnt, 0)
FROM      (SELECT 'Jenny' AS name FROM dual
           UNION ALL
           SELECT 'Penny' FROM dual
           UNION ALL
           SELECT 'George' FROM dual
           UNION ALL
           SELECT 'James' FROM dual
           UNION ALL
           SELECT 'Jessica' FROM dual
           UNION ALL
           SELECT 'Monica' FROM dual
           UNION ALL
           SELECT 'Erica' FROM dual) rn
LEFT JOIN (SELECT   name, COUNT(*) AS cnt
           FROM     namestable
           WHERE    adeddate BETWEEN '2014/10/15' AND '2014/10/16' 
           GROUP BY name) n ON n.name = rn.name

Comments

[xQbert]

so does namestable have values in it for jessica monica and erica? if not you're saying you want your filter to add values to your result set when they were not there in the first place? That's simply not how SQL works. You have to have a table/set with this data in it first. This could be done via union, cte, or temp tables.