CODEWITHSUNDEEP
sqloracle-database
user2862073
user2862073

Reputation: 254

Return Month wise count if no data for month return 0 as count in oracle sql

I have a table having data for January to March (till current month) and I am able to take the month wise count.But user required is to display zero for rest of the month.Kindly suggest.

For example:

select count(a.emp_id) as cnt ,to_char(a.due_date,'MONTH') as Process_Month from EMP_Request a
where a.due_date is not null
group by to_char(a.due_date,'MONTH')

Output:

cnt    Process_month

20     JANUARY

35     FEBUARY

26     March

Desired output:

cnt    Process_month

20     JANUARY

35     FEBUARY

26     March

0      APRIL

0      MAY
…….
….
….
0      DECEMBER

Please assist.

Upvotes: 0

Views: 611

Answers (3)

Ponder Stibbons
Ponder Stibbons

Reputation: 14848

Right join months generator with your query:

select to_char(to_date(mth_num, 'MM'), 'MONTH') month, nvl(cnt, 0) cnt
  from (
    select count(emp_id) as cnt, to_char(due_date, 'mm') mth_num
      from emp_request where due_date is not null
      group by to_char(due_date, 'mm')) e
  right join (
    select to_char(level, 'fm00') mth_num 
      from dual connect by level <= 12) m using (mth_num)
  order by mth_num

dbfiddle demo

Months generator is a simple hierarchical query which gives us 12 values 01, 02... 12:

select to_char(level, 'fm00') mth_num from dual connect by level <= 12

You can also use system views to get these numbers:

select to_char(rownum, 'fm00') mth_num from all_objects where rownum <= 12

or this syntax:

select to_char(column_value, 'fm00') mth_num 
  from table(sys.odcivarchar2list(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12))

It's better to work on numbers which you can sort properly and convert to month names in the last step. This way you have natural months order. If you want to be sure that month names are always in english, not dependent from local settings then use to_date with third parameter, like here:

select to_char(sysdate, 'month', 'nls_date_language=english') from dual

Upvotes: 0

F.Madsen
F.Madsen

Reputation: 702

This is a general problem which is not really a sql problem. SQL doesn't really know about what months you are interested in. So the solution is to tell it in a sub query.

Here is a solution that doesn't use external tables. You simply select all months of the year and outer join your data.

select TO_CHAR(TO_DATE(available_months.m,'MM'),'MONTH') , NVL(sum(data.cnt),0) from
(select to_number(to_char(sysdate,'MM')) m, 7 cnt from dual) data,
(select 1 m from dual union select 2 from dual union select 3 from dual union select 4 from dual
                      union select 5 from dual union select 6 from dual union select 7 from dual 
                      union select 8 from dual union select 9 from dual union select 10 from dual 
                      union select 11 from dual union select 12 from dual) available_months
where 
  data.m (+) = available_months.m
group by available_months.m
order by available_months.m;

Or with your data query included is should look like (not tested):

select TO_CHAR(TO_DATE(available_months.m,'MM'),'MONTH') , NVL(sum(data.cnt),0) from
(select count(a.emp_id) as cnt ,to_char(a.due_date,'MONTH') as Process_Month from EMP_Request a where a.due_date is not null) data
(select 1 m from dual union select 2 from dual union select 3 from dual union select 4 from dual
                      union select 5 from dual union select 6 from dual union select 7 from dual 
                      union select 8 from dual union select 9 from dual union select 10 from dual 
                      union select 11 from dual union select 12 from dual) available_months
where 
  data.due_date (+) = available_months.m
group by available_months.m
order by available_months.m;

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 31993

use WWV_FLOW_MONTHS_MONTH to get all the month and left join with your query to get the month name from the date column and join with it

  with cte
  (
   SELECT month_display as month FROM WWV_FLOW_MONTHS_MONTH
  ) , cnt as
  (
  select count(a.emp_id) as cnt ,
 to_char(a.due_date,'MONTH') as Process_Month from EMP_Request a
  where a.due_date is not null
 group by to_char(a.due_date,'MONTH')
 ) select coalesce(Process_Month,month), cnt from cte left join cnt on cte.month=cnt.to_char(to_date(Process_Month, 'DD-MM-YYYY'), 'Month')

Upvotes: 2

Related Questions