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
Comments
Post a Comment