MySQL. How can I COUNT() but insert different value on the different column?-CodePudding

table users

|   id   |   username   |   status   |   
|   1    |   cahya      |   steady   |
|   2    |   kadek      |   steady   |
|   3    |   cahya      |   steady   |
|   4    |   erno       |   ready    |
|   5    |   kadek      |   go       |

hopping result is like below

| username | steady | ready | go |
|  cahya   |   2    |   0   | 0  |
|  kadek   |   1    |   0   | 1  |
|  erno    |   0    |   1   | 0  |

I did it separated but I think it is not a good work

select username, count(status) from users where status = 'steady'
select username, count(status) from users where status = 'ready'
select username, count(status) from users where status = 'go'

CodePudding user response：

SELECT username, 
       SUM(status = 'steady') AS steady, 
       SUM(status = 'ready') AS ready, 
       SUM(status = 'go') AS go
FROM users 
GROUP BY 1;