+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: General Help (https://forum.codeigniter.com/forumdisplay.php?fid=24)
+--- Thread: Arranging by months_total query (/showthread.php?tid=818)
Arranging by months_total query - rajneeshgobin - 01-20-2015
hello , in my senario, students can make entries in the database whole year round entering the number of cash they spent on diferent items.
basically the table is like this
id,timestamp,username,itembought,price
and some sample data is
2, 2014-01-20 13:55:31 , raj, piza, 50
3, 2014-01-21 14:53:32 , raj, juice, 10
4, 2014-01-21 11:52:33 , rita, rice, 150
5, 2014-02-21 11:51:34 , raj, noodle, 250
.
.
.
5000, 2015-01-12 17:50:38 , rani, fish, 40
now i need a yearly report for each student per months(total)
something like this
2014
january february ... december total
raj 150 100 100 2500
rita 150 200 28 3000
rani 100 200 200 2580
how can i write my query to get this ? or any suggestion to achive this?
RE: Arranging by months_total query - rajneeshgobin - 01-21-2015
$query = $this->db->query("
select groups.id,groups.groupname,
sum(if( MONTH(timestamp) = 1, price, 0 )) as January,
sum(if( MONTH(timestamp) = 2, price, 0 )) as February,
sum(if( MONTH(timestamp) = 3, price, 0 )) as March,
sum(if( MONTH(timestamp) = 4, price, 0 )) as April,
sum(if( MONTH(timestamp) = 5, price, 0 )) as May,
sum(if( MONTH(timestamp) = 6, price, 0 )) as June,
sum(if( MONTH(timestamp) = 7, price, 0 )) as July,
sum(if( MONTH(timestamp) = 8, price, 0 )) as August,
sum(if( MONTH(timestamp) = 9, price, 0 )) as September,
sum(if( MONTH(timestamp) = 10, price, 0 )) as October,
sum(if( MONTH(timestamp)=11, price, 0 )) as November,
sum(if( MONTH(timestamp) = 12, price, 0 )) as December,
sum(price) as Total
from groups left join purchase on groups.id = purchase .usergroup
where (YEAR(timestamp) ='$year')
group by groups.id,
groups.groupname
");
i had to slightly modify the structure of the table
where i include group which has the name and id of student
and purchase table is like this
2, 2014-01-20 13:55:31 , 1, piza, 50
3, 2014-01-21 14:53:32 , 2, juice, 10
4, 2014-01-21 11:52:33 , 1, rice, 150
5, 2014-02-21 11:51:34 , 1, noodle, 250
instead of the name
the solution works but it is not very neat, is there a code igniter way to do this?