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:

[1] => [1] => [March 2011] [20]

           => [April 2011] [5]

       [2] => [March 2011] [0]

           => [April 2011] [5]

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!

Accepted Answer

Sample table

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.

This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki