Month grouping - get count for each month

Question

Looking to get record counts for each month. However, several months has no records therefore no row is returned. How can I get a count of 0 for that month?

select months, count(rowid) as counter from (
    select  to_char(date_entered, 'MM') as months
    from mydatatable
    where to_char(date_entered, 'yyyy') = '2011'
     )
group by months
order by months

Result:

Month    Count
01       32
03       12
04       11
06       10
07       222
08       32

Even tried playing with subq select 1,2,3,4,5,6,7,8,9,10,11,12 from dual and could not get it to work. No pivot capability yet... ;(

Answer 1

this my example where DAT_ULT_ALT is like an update date:

SELECT TO_CHAR(TMP_ULT_ALT, 'YYYY-MM') ano_mes , count(*) FROM tabela group by ano_mes order by 1

Answer 2

This is very strange... Maybe I misunderstood the question or data...? It is always good idea to add tables and data to your questions. You should get all data for all months with count. I tried this:

SELECT * FROM stack_test
/
CURR_MONTH  VAL
---------------
01          10
02          15
03          20
04  
05  

As you can see months 4 and 5 have no values:

 SELECT months, COUNT(rowid) counter 
   FROM
    (
     SELECT curr_month months
       FROM stack_test
    )
 GROUP BY months
 ORDER BY months
 /
 MONTHS COUNTER
 -------------------
 01           1
 02           1
 03           1
 04           1
 05           1

And another example: month 2 has no value but I still get count of course. Maybe you need to sum up your values...:

SELECT mth, SUM(val) total_sum, Count(*) total_cnt 
  FROM 
  (
  SELECT mth, (CASE WHEN Mth = '01' THEN '10' ELSE '0' END) val
    FROM
    ( -- Annual table - replace 2 with 12 in Add_Months for the whole year -- 
     SELECT Trunc(SYSDATE,'Y')+Level-1 Curr_Year_By_Date
        , To_char(Trunc(SYSDATE, 'MM') + Rownum-1, 'MM' ) Mth
     FROM dual
    CONNECT BY Level <= Add_Months(Trunc(SYSDATE,'Y'),2)-Trunc(SYSDATE,'Y')
   )
  )
 GROUP BY mth
 ORDER BY 1
 /
 MTH    TOTAL_SUM   TOTAL_CNT
 -------------------------------------
 01         310         31
 02         0           28

Answer 3

You can build a dummy table containing the month numbers using the connect by syntax of a hierarchical query, and then left-join to your data:

with months as (
    select to_char(level, 'FM00') as month
    from dual
    connect by level <= 12
)
select m.month,
    count(mdt.rowid) as counter
from months m
left join mydatatable mdt
    on mdt.date_entered >= to_date('01/' || m.month || '/2011', 'DD/MM/YYYY')
    and mdt.date_entered <
        add_months(to_date('01/' || m.month || '/2011', 'DD/MM/YYYY'), 1)
group by m.month
order by m.month;

With some made up data:

create table mydatatable (date_entered date, dummy number);
insert into mydatatable values (date '2011-06-02', 0);
insert into mydatatable values (date '2011-07-01', 0);
insert into mydatatable values (date '2011-10-01', 0);
insert into mydatatable values (date '2011-10-31', 0);
insert into mydatatable values (date '2011-11-01', 0);

... this gives:

MONTH COUNTER
----- -------
01          0 
02          0 
03          0 
04          0 
05          0 
06          1 
07          1 
08          0 
09          0 
10          2 
11          1 
12          0 

Or SQL Fiddle as that seems to be the thing to do these days...

---

It's generally better to avoid something like to_char(date_entered, 'yyyy') = '2011' because you're applying the to_char() function to every row in the table, and if there is an index on that column then it won't be used. Instead try to convert your filter to match the column's data type, like date_entered > date '2011-01-01' and date_entered < date '2012-01-01'. In this case it can be taken care of in the join condition anyway - I'm converting each month into a date range in 2011, and only looking for matching records within that month range.

Answer 4

You're probably better off storing the 01 to 12 in a table, but the general approach is to use a left join:

Select
  m.Mo,
  Count(t.dateentered)
From (
    Select '01' As Mo From Dual Union All
    Select '02' From Dual Union All
    Select '03' From Dual Union All
    Select '04' From Dual Union All
    Select '05' From Dual Union All
    Select '06' From Dual Union All
    Select '07' From Dual Union All
    Select '08' From Dual Union All
    Select '09' From Dual Union All
    Select '10' From Dual Union All
    Select '11' From Dual Union All
    Select '12' From Dual
  ) m
    Left Outer Join
  mydatatable t
    On
      m.Mo = to_char(t.dateentered, 'MM') And 
      t.dateentered >= DATE'2011-01-01' And
      t.dateentered < DATE'2012-01-01'
Group By
  m.Mo
Order By
  m.Mo

Update used a more index friendly way of restricting the year.

http://sqlfiddle.com/#!4/68085/10

Answer 5

You'll need to build your own 12-row month "table" and perform a left outer join. Take the query from your question and make it an inline view to supply the data.

SELECT m.month "Month", nvl(md.data, 0) "Count"
FROM
(
   select '01' month from dual union all
   select '02' month from dual union all
   select '03' month from dual union all
   select '04' month from dual union all
   select '05' month from dual union all
   select '06' month from dual union all
   select '07' month from dual union all
   select '08' month from dual union all
   select '09' month from dual union all
   select '10' month from dual union all
   select '11' month from dual union all
   select '12' month from dual
) m LEFT OUTER JOIN (
   /* Your Query Here */
) md ON m.month = md.month
ORDER BY m.month;

The results should be something like this:

Month       Count
------ ----------
01             32
02              0
03             12
04             11
05              0
06             10
07            222
08             32
09              0
10              0
11              0
12              0