MySQL select count by value

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?

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

I think with SUM() you can get neater code. Since it sums the values respective expression for row.

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

Official Documentation can be found here.

SELECT `date`, COUNT(*) AS `COUNT TOTAL`, 
  COUNT(CASE `value` WHEN 1 THEN `value` END) AS `COUNT VAL=1`
  COUNT(CASE `value` WHEN 2 THEN `value` END) AS `COUNT VAL=2`
FROM mytable
GROUP BY `date`

The CASE expressions will be null when there is no match. Nulls are not counted by COUNT().

I imagine you might want a dynamic number of columns, one column for each value found in the data. This is not possible in SQL. The columns must be known at the time you write the query.

So you have two options to get subtotals per value:

• First query the distinct values from all rows of value and construct an SQL query dynamically, appending one column in the select-list for each distinct value you want to report. Then run this SQL query.

• Alternatively, fetch all the rows as rows. Count the subtotals per value in application code.

One further alternative is to count subtotals by groups, and include totals:

SELECT `value`, `date, COUNT(*) AS `COUNT SUBTOTAL`
FROM mytable
GROUP BY `value`, `date` WITH ROLLUP

But that doesn't report the subtotals in columns as you requested, it reports the subtotals in rows.