GROUP BY query

[spookie]

Hello,

The query is like

select count(*), Date_Format(application_date, '%Y/%M') as month from tablename where commn_date IS NULL
AND application_date >= '$date_from1' AND application_date <= '$date_to1'
group by Date_Format(appilcation_date, '%Y/%M') order by application_date

The record is get is something like
2 2003/February
4 2003/March
5 2003/April/
etc.
Now if there is no matching record in the month of 2003/January the result will not be getting fetched.
however for display purpose i want
0 2003/January
Can in some way i modify the above query to include the result which is having 0 records?
I am clueless at the moment.

Thanks in advance



--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[swampBoogie]

You need an auxillary table that contains all combinations of year and month that you are interested in.

create table aux( ym varchar(20) primary key)
--
insert into aux values('2003/January') 
-- etc
select count(ym), Date_Format(application_date, '%Y/%M') as month from aux left join tablename 
on aux.ym = Date_Format(application_date, '%Y/%M')
where commn_date IS NULL 
AND application_date >= '$date_from1' 
AND application_date <= '$date_to1'
group by Date_Format(appilcation_date, '%Y/%M') 
order by application_date

 

[spookie]

I tried wht u suggested swampBoogie , but still i am able to get only those records which have non zero values..
Any suggestions?
Thanks

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.

 

[spookie]

Any workaround for that?

--------------------------------------------------------------------------
I never set a goal because u never know whats going to happen tommorow.