Oracle SELECT COUNT(*) … GROUP BY … not returning rows where the count is zero

Question

I tried the solution given in "mysql SELECT COUNT(*) … GROUP BY … not returning rows where the count is zero" but it didn't work for me.

Here is SQL ::

SELECT empid, SUM (total_records) total_records  
  FROM (SELECT ID,  
               (CASE WHEN ID LIKE '2%'  THEN '2____'  
                     WHEN ID LIKE '3%'  THEN '3____'  
                     WHEN ID LIKE '99%' THEN '99____'  
               END) empid,  
               total_records  
          FROM tr  
         where ( id like '2%' or id like '3%' or id like '99%'))  
GROUP BY empid;  

When the particular value in empid SUM is ZERO, that row isn't shown at the output. This shows we have no data (or sum) for that particular empid. But is it possible to have '0' for that particular value to be displayed in the result set ?

Answer 1

Not with this query, you would need to union its result set with another query that finds the missing values.

Answer 2

I think you are looking for LEFT JOIN. Use that instead of your nested SELECT statements. I don't have an example for you, but this website might help you.

Answer 3

Adding detail to both ivanpro and Michael Goldshteyn's answers, here is a demo with a dummy 'table' with the three desired empid values outer joined against the original query

SELECT a.empid, nvl(SUM (total_records),0) total_records  
  FROM (select '2____' empid from dual union select '3____' from dual 
                                       union select '99____' from dual) a 
       left outer join
       (SELECT ID,  
               (CASE WHEN ID LIKE '2%'  THEN '2____'  
                     WHEN ID LIKE '3%'  THEN '3____'  
                     WHEN ID LIKE '99%' THEN '99____'  
               END) empid,  
               total_records  
       FROM tr 
       where ( id like '2%' or id like '3%' or id like '99%')) b 
     on a.empid = b.empid
GROUP BY a.empid;