mysql - Find number of "active" rows each month for multiple months in one query -
i have mysql database each row containing activate , deactivate date. refers period of time when object row represents active. activate deactivate id 2015-03-01 2015-05-10 1 2013-02-04 2014-08-23 2 i want find number of rows active @ time during each month. ex. jan: 4 feb: 2 mar: 1 etc... i figured out how single month, i'm struggling how 12 months in year in single query. reason in single query performance, information used , caching wouldn't make sense in scenario. here's code have month @ time. checks if activate date comes before end of month in question , deactivate date not before beginning of period in question. select * tblname activate <= date_sub(now(), interval 1 month) , deactivate >= date_sub(now(), interval 2 month) if has idea how change , grouping such can indefinite number of months i'd appreciate it. i'm @ loss how group. if have table of months care about, can do: select m.*, (select cou...