Tawhidul Islam Tawhidul Islam - 3 months ago 7
MySQL Question

How would you tidy up this daily report Mysql logic

I'm showing daily basis data. Say One Human(HumanID) daily eat 2 times morning , evening. So i input data like this.

Table: report

--------------------------------------
ID | HumanID | date | schedule | amount|
--------------------------------------
1 | 101 | 2016-01-01 | morning | 10 |
2 | 101 | 2016-01-01 | evening | 8 |
3 | 102 | 2016-01-01 | morning | 11 |
4 | 102 | 2016-01-01 | evening | 9 |
5 | 103 | 2016-01-01 | morning | 8 |
6 | 103 | 2016-01-01 | evening | 7 |


Query Result will be:
I want display like this

---------------------------------------
HumanID | date | morning | evening |
---------------------------------------
101 | 2016-01-01 | 10 | 8 |
102 | 2016-01-01 | 11 | 9 |
103 | 2016-01-01 | 8 | 7 |

Answer

This is a pivot query. In MySQL, you do this with conditional aggregation:

select id, date,
       max(case when schedule = 'morning' then amount end) as morning,
       max(case when schedule = 'evening' then amount end) as evening
from t
group by id, date;