CODEWITHSUNDEEP
mysql
Tawhidul Islam
Tawhidul Islam

Reputation: 362

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     |

Upvotes: 1

Views: 18

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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;

Upvotes: 1

Related Questions