php - MySQL query to return counts of rows by month -

i'm trying counts of number of rows in table last 12 months, on month month basis give 12 counts.

so far, have ugly way of doing it:

select (     select count(i.id) intrusions i, devices d i.device_id = d.id , d.primary_owner_id = '1' , from_unixtime(start_time/1000) '2014-04%',     select count(i.id) intrusions i, devices d i.device_id = d.id , d.primary_owner_id = '1' , from_unixtime(start_time/1000) '2014-03%',     select count(i.id) intrusions i, devices d i.device_id = d.id , d.primary_owner_id = '1' , from_unixtime(start_time/1000) '2014-02%',     etc )   

the tables set 1 user can have many devices , 1 device can have many intrusions, hence conditions.

the primary_owner_id , date added in dynamically in php using prepared statements. there better way write out wouldn't involve repitition , binding 24 parameters? appreciated

you should use grouping this. this.

select     concat(year(from_unixtime(start_time/1000)), '-', month(from_unixtime(start_time/1000))) `year_month`,     count(id) `count` intrusions inner join devices d   on i.device_id = d.id d.primary_owner = ? group `year_month` order `year_month` desc limit 12