sql - complicated column calculation in mysql to get a count of monthly visits -

i have 2 tables this

customer table

first last cust_id   john doe 0   jane doe 1   

ledger table

posted_date cust_id    2014-01-14  0   2014-01-20  0   2013-12-20  0   2013-12-20  1   2013-11-12  1   2013-11-10  1 

i need calculate number of months customer posted transaction @ least once, being called customermonths last 12 months. means customermonths each cust_id between 0 , 12. data want see

cust_id customermonths   0 2   1 2 

this because cust_id 0 in @ least once in jan 2014 , @ least once in dec 2013.
similarly, cust_id 1 in @ least once in dec 2013 , @ least once in nov 2013.
example cust_id 0:

2014-01-14,   2014-01-20 = 1 customermonths   2013-12-20 = 1 customermonths   

so total customermonths last 12 months cust_id 0 2.

i have working 1 month not sure how work last 12 months. although i'd settle working last 2 months. think figure out rest. here's have.

select distinct      c.cust_id,     (case when count(ljan.posted_date) = 0 0 else      case when count(ljan.posted_date) > 0 1 end end) customermonths 'customer' c left join 'ledger' ljan on (ljan.cust_id = c.cust_id , ljan.posted_date between '2014-01-01' , '2014-01-31') group c.cust_id 

you need count distinct months, use count(distinct). question argument. try this:

select c.cust_id,        count(distinct year(l.posted_date) * 100 + month(l.posted_date)) customermonths customer c left join      ledger l      on l.cust_id = c.cust_id ,         l.posted_date between '2013-01-01' , '2014-01-31' group c.cust_id; 

another way of writing select:

select c.cust_id,        count(distinct date_format(l.posted_date, '%y-%m')) customermonths