sum count() by date and group by on while php

Question

i have this on mysql table :

user    |    open    |    date   
--------------------------------- 
User1   |     1      | 2017-05-19 
User2   |     1      | 2017-05-19 
User3   |     1      | 2017-05-19 
User4   |     1      | 2017-05-19 
User1   |     3      | 2017-05-18 
User2   |     5      | 2017-05-18 
User4   |     20     | 2017-05-17 
User1   |     2      | 2017-05-17 
User2   |     7      | 2017-05-17

i search to make result while data mysql :

open = 2017-05-19

open2 = 2017-05-18

open3 = 2017-05-17

user    |    open    |    open2   |    open3   
---------------------------------------------
User1   |     1      |      3     |      2    
User2   |     1      |      5     |      7   
User3   |     1      |      0     |      0   
User4   |     1      |      0     |      20

you have idea ???

i have group by user but after its the problem !!!

thank's by advance for your help.

Answer 1

you can use case when

  select user, 
      ifnull(case when date  = '2017-05-19' then open end , 0)as open, 
      ifnull(case when date  = '2017-05-18' then open end, 0) as open2, 
      ifnull(case when date  = '2017-05-17' then open end,0)  as open3, 
  from my_table 

Answer 2

SELECT user,
SUM(IF(date='2017-05-19',open,0)) AS open1,
SUM(IF(date='2017-05-18',open,0)) AS open2,
SUM(IF(date='2017-05-17',open,0)) AS open3
FROM SomeTableName
GROUP BY user;