How to write mysql query for getting the below result

Question

I have two tables

1) students
2) fees_paid

student table is as follows

id              Name
 1              xxxxxx
 2              yyyyyy
 3              zzzzzz

fees_paid is as follows

id    student_id           date            fees_paid
1      1                02-01-2015             250
2      1                05-01-2015             500
3      2                07-01-2015             400
4      1                06-02-2015             100
5      2                08-02-2015             200
6      3                04-05-2015             1000

And I need a result table as below

Name         Jan       Feb     Mar    April     May


xxxxxx      750       100       0        0       0
yyyyyy      400       200       0        0       0
zzzzzz      0         0         0        0      1000

How to write a mysql query for getting the above result set

What do you mean by Codeigniter query ? ActiveRecord ? PDO ? And uh, what have you tried so far ? — BMN, Jul 23 '15 at 09:06
Normal MYSQL Query is also ok. I actually dont have an idea how to make this in a single query. But I can do it in multiple queries — S Vinesh, Jul 23 '15 at 09:08
you will get some idea from this: http://stackoverflow.com/questions/1241178/mysql-rows-to-columns — Suresh Kamrushi, Jul 23 '15 at 09:09

score 5 · Accepted Answer · answered Jul 23 '15 at 09:11

5

Try this :

SELECT
    s.name,
    sum(case when month(f.date) = '01' then f.fees_paid else 0 end) as 'Jan',
    sum(case when month(f.date) = '02' then f.fees_paid else 0 end) as 'Feb',
    sum(case when month(f.date) = '03' then f.fees_paid else 0 end) as 'Mar',
    sum(case when month(f.date) = '04' then f.fees_paid else 0 end) as 'April',
    sum(case when month(f.date) = '05' then f.fees_paid else 0 end) as 'May'
FROM fees_paid f 
INNER JOIN students s ON s.id = f.student_id
GROUP BY s.id

answered Jul 23 '15 at 09:11

BMN

8,253
14
48
80

Great Yellow Bird ! You are awesome. This is what I want exactly. You nailed it man! Thanku – S Vinesh Jul 23 '15 at 09:28
But this query will not return a students name who havent payed fees at all, ryt ? But I even want there names to be displayed with fees paid as zero for all months. Any idea? – S Vinesh Jul 23 '15 at 09:50
I got that too... I just did a left join – S Vinesh Jul 23 '15 at 09:56

Malith Wijethunga · Answer 2 · 2015-07-23T09:58:28.107

Try this,

$this->db->select('s.name,
sum(case when month(f.date) = '01' then f.fees_paid else 0 end) Jan,
sum(case when month(f.date) = '02' then f.fees_paid else 0 end) Feb,
sum(case when month(f.date) = '03' then f.fees_paid else 0 end) Mar,
sum(case when month(f.date) = '04' then f.fees_paid else 0 end) April,
sum(case when month(f.date) = '05' then f.fees_paid else 0 end) May');
$this->db->from('fees_paid');
$this->db->join('student', 'student.id = fees_paid.id', 'left');
$this->db->group_by("s.id"); 

$query = $this->db->get();

How to write mysql query for getting the below result

2 Answers2