How to write a mysql query to get the result below

advertisements

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


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