Optimizing a big MySQL aggregate query for analytics -

i'm trying build out basic marketing analytics tools , want provide "transactions date @ day n" summary each campaign code.

is there way make query more efficient? each day_n column want count transactions made before or on day.

select  c.campaign_code,  (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 1) day_1, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 2) day_2, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 3) day_3, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 4) day_4, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 5) day_5, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 6) day_6, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 7) day_7, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 14) day_14, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 30) day_30, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 60) day_60, (select count(*) _t_transactions campaign_code=c.campaign_code , day <= 90) day_90 campaigns c left join _t_transactions t on c.campaign_code=t.campaign_code 

table structure is...

create temporary table `campaigns` ( `campaign_code` varchar(255) default null, );  create table `_t_transactions` ( `id` int(11) default null, `campaign_code` varchar(255) default null, `day` int(11) default null ); 

so question wasn't quite representative--what needed accomplish roi calculation after n days. turns out conditional @tandu suggested works perfectly, , returns in 0.5 seconds instead of 33+ seconds (for small data set).

and of course, tandu points out, not job mysql.

here's ended with:

select  c.campaign_code, c.clicks, c.cpc, c.spent, sum(if(day <= 1, amount, 0)) / c.spent day_1, sum(if(day <= 2, amount, 0)) / c.spent day_2, sum(if(day <= 3, amount, 0)) / c.spent day_3, sum(if(day <= 4, amount, 0)) / c.spent day_4, sum(if(day <= 5, amount, 0)) / c.spent day_5, sum(if(day <= 6, amount, 0)) / c.spent day_6, sum(if(day <= 7, amount, 0)) / c.spent day_7, sum(if(day <= 14, amount, 0)) / c.spent day_14, sum(if(day <= 30, amount, 0)) / c.spent day_30, sum(if(day <= 60, amount, 0)) / c.spent day_60, sum(if(day <= 90, amount, 0)) / c.spent day_90 _t_transactions t left join campaigns c on c.campaign_code=t.campaign_code  group t.campaign_code; 

didn't realize conditions used in aggregate functions... useful in case. solves related problem me of wanting use temporary tables this, not being able reference same temp table multiple times in same query.