MYSQL select count by value

Question

I have this mysql table:

DATE | VALUE

and I wish to become a select which shows me this information as:

DATE | COUNT TOTAL | COUNT VAL=1 | COUNT VAL=2

Any ideas how I can achieve this?

Answer 1

SELECT date,
       COUNT(*),
       COUNT( IF( value = 1, 1, NULL ) ),
       COUNT( IF( value = 2, 1, NULL ) )
FROM my_table