MYSQL select count by value

Question

I have this mysql table:

DATE | VALUE 

and I want to be elected, which shows me this information as:

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

Any ideas how I can achieve this?

Thanks!

Answer 1

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

Answer 2

I think using SUM() you can get cleaner code. Because it sums up the values ​​corresponding to the expression for the string.

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

The official documentation can be found here .

Answer 3

 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` 

CASE expressions will be null if there is no match. Zeros do not count COUNT() .

I assume that you might need a dynamic number of columns, one column for each value found in the data. This is not possible in SQL. Columns must be known at the time of writing.

So, you have two options for getting subtotals for the value:

• First, request different values ​​from all the value rows and build the SQL query dynamically by adding one column to the selection list for each individual value that you want to report. Then run this SQL query.

• Alternatively, select all rows as rows. Count subtotals for each value in the application code.

Another alternative is the calculation of intermediate results by groups and includes totals:

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

But this does not report subtotals in the columns as you requested, it reports subtotals in rows.