mysql how to count each current value in 1 column and split to 3 column

Question

my table name is 'complain'

+---------------+-------------------+
| date_complain | kategory_complain |
+---------------+-------------------+
| 2019-01-01    | green             |
| 2019-01-01    | green             |
| 2019-01-01    | yellow            |
| 2019-01-02    | yellow            | 
| 2019-01-02    | red               |
+---------------+-------------------+

i want count each green, yellow and red then order by date_complain asc

+---------------+-------+--------+-----+
| date_complain | green | yellow | red |
+-------------- +-------+--------+-----+
| 2019-01-01    | 2     | 1      | 0   |
| 2019-01-02    | 0     | 1      | 1   |
+---------------+-------+--------+-----+

i try if count, union and its not work.

Answer 1

Can you try below -

SELECT date_complain, 
       Count(CASE 
               WHEN kategory_complain = 'red' THEN 1 
               ELSE 0 
             END) AS red, 
       Count(CASE 
               WHEN kategory_complain = 'yellow' THEN 1 
               ELSE 0 
             END) AS yellow, 
       Count(CASE 
               WHEN kategory_complain = 'green' THEN 1 
               ELSE 0 
             END) AS green 
FROM   mytable 
GROUP  BY date_complain;