mySQL, multiple COUNT INNER JOINs with GROUP BY DATE() -

i have table of install logs , want select table

1. number of installs per day status = 1
2. number of installs per day status = 0
3. number of installs per day status in(0, 1)

field created_utc keep install unix timestamp.

this table structure

create table `installs` (   `created_utc` int(11) default null,   `status` tinyint(1) default '1' comment '1 - installed, 0 - deleted' ) engine=innodb default charset=utf8; 

and query, not work correctly (i'm selecting u.id crutch execute inner joins... i'm totally lost)

select     u.id,     count(pa.created_utc) installs_this_day, unix_timestamp(from_unixtime(pa.created_utc, '%y-%m-%d'))*1000 time_utc,     count(pa2.created_utc) installs_active_this_day      users u inner join installs pa on pa.status = 0 inner join installs pa2 on pa2.status = 1 u.id = 1 group date(from_unixtime(pa.created_utc)), date(from_unixtime(pa2.created_utc)) 

this results in duplicates , incorrect counts.

1   45  1430280000000   45 1   36  1430280000000   36 1   117 1430280000000   117 1   72  1430280000000   72 1   36  1430280000000   36 1   531 1430280000000   531 1   144 1430280000000   144 1   36  1430280000000   36 1   18  1430280000000   18 1   27  1430280000000   27 1   9   1430280000000   9 

sample of installs table data

insert `installs` (`created_utc`, `status`) values     (1430320706, 0),     (1430319316, 0),     (1430311682, 0),     (1430311506, 0),     (1430311498, 0),     (1430311498, 0),     (1430311386, 0),     (1430311370, 0),     (1430311356, 0),     (1430311272, 1),     (1430278530, 1),     (1430229788, 1); 

i think want conditional aggregation, not 2 or 3 joins. this:

select unix_timestamp(from_unixtime(pa.created_utc, '%y-%m-%d'))*1000 time_utc,        sum(pa.status in (0, 1)) total_installs,        sum(pa.status = 0) installs_status0,        sum(pa.status = 1) installs_status1 installs pa group unix_timestamp(from_unixtime(pa.created_utc, '%y-%m-%d'))*1000;