3

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.

TechDo
  • 18,398
  • 3
  • 51
  • 64
user1979831
  • 67
  • 1
  • 7
  • You need to join a _sub-select_ table that contains all months range. Then display the count(time_stamp) grouped by all listed months. Here you can see an exemple of how to populate a sub-select table with month : http://stackoverflow.com/questions/4375897/connected-by-months or http://stackoverflow.com/questions/4644562/oracle-sql-query-to-list-all-the-dates-of-previous-month – Ryx5 Jun 09 '14 at 11:00

1 Answers1

1

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

valex
  • 23,966
  • 7
  • 43
  • 60
  • Thanks for the help but the output is incorrect. Using your query, output is (0,0,7050,0). However, the count of month may is 5274 using the below query : select count(time_stamp) from ps_bqueues_host where time_stamp BETWEEN TO_DATE('01-May-14', 'DD-Mon-YY') and TO_DATE('31-May-14', 'DD-Mon-YY'). The expected result is (0,0,5274,0) – user1979831 Jun 10 '14 at 03:55
  • Does time_stamp field contain time part? If so then your query doesn't count the latest day of 31.05.2014 try to use `and TO_DATE('01-Jun-14', 'DD-Mon-YY')` instead of `31 May 2014`. – valex Jun 10 '14 at 05:34
  • That's right. '01-Jun-14' worked. Please suggest how to handle this? I mean is there a way to include the last day data also without querying till the next day? – user1979831 Jun 10 '14 at 09:21
  • It is because `31-May-2014 23:00:01` > '31-May-2014'. You should add a day for example `...and TO_DATE('31-May-14', 'DD-Mon-YY')+1` or add time `..and To_date('31-May-2014 23:59:59','mm-Mon-yy hh24:mi:ss')` – valex Jun 10 '14 at 10:13
  • I opted To_date('31-May-2014 23:59:59','mm-Mon-yy hh24:mi:ss') and it works. Thanks for the help! – user1979831 Jun 11 '14 at 04:10