mysql - Get count of entries per period excluding first one (SQL query) -

mysql - Get count of entries per period excluding first one (SQL query) -

i have create sql query, give me count of buyers per month, made @ to the lowest degree 1 purchase in month, not counting buyer's first purchase ever.

for example, have table:

id bill_date 1 2014-01-14 1 2014-02-14 2 2014-02-14 2 2014-02-18 1 2014-02-19 2 2014-03-14 1 2014-03-14 1 2014-03-16 1 2014-04-08 1 2014-06-03 2 2014-06-10 1 2014-06-11 3 2014-11-07 3 2014-11-13

therefore:

jan - 1 bill id1 feb - 2 bills id2, 2 bills id1 mar - 1 bill id1, 2 bills id2 apr - 1 bill id1 jun - 2 bills id1, 1 bill id2 nov - 2 bills id3

expected results:

period accounts 2014-02 2 2014-03 2 2014-04 1 2014-06 2 2014-11 1

basically, since id1 made purchase in january, distinct count each month after jan appear.

since id2 made 2 purchases in february, count 1 in february, , 1 every month after (even if made multiple purchases).

for id3 made 2 purchases in november, count them 1 november. had made 1 purchase in november, , in december, dec show one, nov not show anything.

thank much in advance!

this may help :

select date_format(t1.bill_date, '%y-%m') dt, count(distinct t1.id) cnt tempt t1 bring together (select id, min(bill_date) bill_date tempt grouping id) t2 on t1.id = t2.id , t1.bill_date <> t2.bill_date grouping date_format(t1.bill_date, '%y-%m')

sql fiddle

mysql sql