I have the following table:
//table_1 record_id user_id plant_id date cost 1 1 1 2011-03-01 10 2 1 1 2011-03-02 10 3 1 1 2011-04-10 5 4 1 2 2011-04-15 5
I would like to build a query (if possible using CI Active Records, but MySQL is fine) in which I generate the following result:
 =>  => [March 2011]  => [April 2011]   => [March 2011]  => [April 2011] 
I have tried using
$this->db->group_by but I think I'm not using it correctly.
If anyone could give me a pointer or roadmap to get this done it would be much appreciated -- thanks!
drop table if exists t; create table t( record_id int, user_id int, plant_id int, date datetime, cost float); insert t select 1 ,1, 1 ,'2011-03-01', 10 union all select 2 ,1, 1 ,'2011-03-02', 10 union all select 3 ,1, 1 ,'2011-04-10', 5 union all select 4 ,1, 2 ,'2011-04-15', 5;
Because you want to see the row with 0, you need to do a cross join between the year-month and all user-plants.
select up.user_id, up.plant_id, ym2, ifnull(sum(t.cost),0) totalcost from (select distinct date_format(date, '%Y-%m') ym, date_format(date, '%M %Y') ym2 from t) dates cross join (select distinct t.user_id, t.plant_id from t) up left join t on date_format(t.date, '%Y-%m') = dates.ym and up.user_id=t.user_id and up.plant_id=t.plant_id group by up.user_id, up.plant_id, ym2, ym order by up.user_id, up.plant_id, date(concat(ym,'-1'));
The fact that
Month Year does not sort correctly also requires the complex treatment of the dates for ordering purposes at the end.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki