MySQL show all months In between date

i, have query

select 
       CONCAT(monthname(a.tanggal), ' ', year(a.tanggal)) as bulan_tahun, 
       sum(a.kg) as kg, 
       sum(a.ka) as ka, 
       sum(a.lr) as lr, 
       sum(a.lh) as lh, 
       sum(a.lb) as lb, 
       sum(a.ll) as ll, 
       sum(a.nc) as nc, 
       sum(a.na) as na, 
       sum(a.sbs) as sbs, 
       开发者_如何学JAVAsum(a.stbs) as stbs, 
       sum(a.kg)+sum(a.ka)+sum(a.lr)+sum(a.lh)+sum(a.sbs)+sum(a.lb)+sum(a.ll)+sum(a.nc)+sum(a.na)+sum(a.stbs) as total 
   from 
      opr.t_temuan as a 
   where 
          a.id_site=197 
      and a.tanggal between '2010-02-01' and '2011-01-31' 
      and (a.type ='f' or a.type = 'i') 
   group by 
      bulan_tahun 
   order by 
      a.tanggal asc

i get return month

June 2010 0 0 5 0 4 6 0 0 3 3 21
July 2010 0 0 99 39 4 4 0 0 2 2 150
August 2010 0 0 62 79 0 5 5 0 0 0 151
September 2010 0 0 1 0 0 0 0 0 0 0 1
November 2010 0 0 59 4 75 1 0 0 8 8 155
December 2010 0 0 1 0 0 0 0 0 0 0 1

but i want return month start january 2010 to december 2010 like this

January 2010 0 0 0 0 0 0 0 0 0 0
Febuary 2010 0 0 0 0 0 0 0 0 0 0
Marc 2010 0 0 0 0 0 0 0 0 0 0
April 2010 0 0 0 0 0 0 0 0 0 0
May 2010 0 0 0 0 0 0 0 0 0 0
June 2010 0 0 5 0 4 6 0 0 3 3 21
July 2010 0 0 99 39 4 4 0 0 2 2 150
August 2010 0 0 62 79 0 5 5 0 0 0 151
September 2010 0 0 1 0 0 0 0 0 0 0 1
November 2010 0 0 59 4 75 1 0 0 8 8 155
December 2010 0 0 1 0 0 0 0 0 0 0 1

please help me !! thanks

The first query uses variables joined with ANY table and limiting to 12 months just so it will auto-generate each month/year you want... ex: starting with 2010-01-01 and going for 12 months. The second query is almost identical to your original, pre-grouping by months that have actual data. Then, by left-joining them, each month in the first will grab what it can find in the final data aggregations, but keep the results based on the date and not alphabetically.

select 
      date_format(justDates.FirstOfMonth,'%M %Y') ShowDate,
      ifnull(PreSummary.kg, 0) as kg,
      ifnull(PreSummary.ka, 0) as ka,
      ifnull(PreSummary.lr, 0) as lr,
      ifnull(PreSummary.lh, 0) as lh,
      ifnull(PreSummary.lb, 0) as lb,
      ifnull(PreSummary.ll, 0) as ll,
      ifnull(PreSummary.nc, 0) as nc,
      ifnull(PreSummary.na, 0) as na,
      ifnull(PreSummary.sbs, 0) as sbs,
      ifnull(PreSummary.stbs, 0) as stbs,
      ifnull(PreSummary.total, 0) as Total
   from
      ( SELECT @dt:= date_add( @dt, interval 1 month ) FirstOfMonth
          FROM (select @dt := '2010-01-01' ) vars,
               opr.t_temuan
               LIMIT 12 ) JustDates
      left join 
      ( Select 
              date_format(a.tanggal,'%M %Y') ShowDate,
              sum(a.kg) as kg,
              sum(a.ka) as ka,
              sum(a.lr) as lr,
              sum(a.lh) as lh,
              sum(a.lb) as lb,
              sum(a.ll) as ll,
              sum(a.nc) as nc,
              sum(a.na) as na,
              sum(a.sbs) as sbs,
              sum(a.stbs) as stbs,
              sum(a.kg+a.ka+a.lr+a.lh+a.sbs+a.lb+a.ll+a.nc+a.na+a.stbs) as total
         from 
            opr.t_temuan as a
         where
                a.id_site=197
            and a.tanggal between '2010-02-01' and '2011-01-31'
            and a.type in ( 'f', 'i')
         group by
             1 ) PreSummary
      on JustDates.ShowDate = PreSummary.ShowDate
  order by
      JustDates.FirstOfMonth