CODEWITHSUNDEEP
sqloracle-databasegroup-by
user1979831
user1979831

Reputation: 67

Oracle query to get month wise data and give 0 for month not available

Following is my table data

time_stamp     Name
01-Mar-14      a
02-Mar-14      b
02-Mar-14      c
01-May-14      d
02-May-14      e
01-Jun-14      f

Output required: 

(3,0,2,1) (Month wise count with 0 if month doesn't exist)

I have created the following query : 

select 
 listagg(count(1),',') within group (order by EXTRACT(month FROM time_stamp)) 
from ps_bqueues_host 
where time_stamp BETWEEN TO_DATE('01-Mar-14', 'DD-Mon-YY') and
  TO_DATE('01-Jun-14', 'DD-Mon-YY') GROUP BY EXTRACT(month FROM time_stamp)

This gives me the output :

(3,2,1) (Month of Apr with 0 is not there).

Please suggest how to group by on all months.

Thanks.

Upvotes: 3

Views: 5582

Answers (1)

valex
valex

Reputation: 24134

You should Join this original table with table with all months in given period. If it is inside one year then we need 1,2,3,...12 sequence.

select 
 listagg(count(Name),',') within 
    group (order by m.rn) 
from 
     (SELECT * FROM  ps_bqueues_host 
      where time_stamp 
        BETWEEN TO_DATE('01-Mar-14', 'DD-Mon-YY') 
        and     TO_DATE('01-Jun-14', 'DD-Mon-YY') 
     )   
RIGHT JOIN 
     (SELECT LEVEL rn FROM dual CONNECT BY LEVEL <= 12) m
     ON m.rn=EXTRACT(month FROM time_stamp)

WHERE m.rn BETWEEN EXTRACT(month FROM TO_DATE('01-Mar-14', 'DD-Mon-YY'))
           AND  EXTRACT(month FROM TO_DATE('01-Jun-14', 'DD-Mon-YY'))
GROUP BY m.rn

SQLFiddle demo

Upvotes: 1

Related Questions