CODEWITHSUNDEEP
sqloracle-databaseselectcountzero
Arno
Arno

Reputation: 344

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

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

Upvotes: 2

Views: 21232

Answers (4)

Mureinik
Mureinik

Reputation: 312219

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

Upvotes: 6

OracleDBA
OracleDBA

Reputation: 1

You could create an a table containing all valid names, eg valid_names table. Then join to your namestable using an outerjoin. Eg:

select valid_names.name, count(1) 
from namestable, valid_names
where valid_names.name=namestable.name (+)
group by valid_names.name
order by valid_names.name;

Upvotes: 0

xQbert
xQbert

Reputation: 35343

This should work with a CTE..

WITH CTE AS
(SELECT 'Jenny' Names 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)
SELECT CTE.names, coalesce(count(NT.Names),0)
FROM  CTE
LEFT JOIN NAMESTABLE NT 
  on CTE.Names = NT.Names
 and adeddate BETWEEN '2014/10/15' AND '2014/10/16' 
GROUP BY CTE.Names

Upvotes: 0

Rimas
Rimas

Reputation: 6024

Then you must use other syntax:

SELECT t.Names, COUNT(n.Names)
FROM (
  SELECT 'Jenny' AS names 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
) t
LEFT OUTER JOIN NAMESTABLE n
  ON n.names = t.names AND n.adeddate BETWEEN '2014/10/15' AND '2014/10/16'
GROUP BY t.names

Upvotes: 0

Related Questions