user1472709 user1472709 - 2 months ago 6
MySQL Question

Count values above threshold for each column in mysql

mysql> show columns from MYTABLE;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| x | int(11) | NO | PRI | NULL | |
| y | int(11) | NO | PRI | NULL | |
| c0 | decimal(3,1) | NO | | NULL | |
| c1 | decimal(3,1) | NO | | NULL | |
| c2 | decimal(3,1) | NO | | NULL | |
| c3 | decimal(3,1) | NO | | NULL | |
| c4 | decimal(3,1) | NO | | NULL | |
| c5 | decimal(3,1) | NO | | NULL | |
| c6 | decimal(3,1) | NO | | NULL | |
| c7 | decimal(3,1) | NO | | NULL | |
| c8 | decimal(3,1) | NO | | NULL | |
| c9 | decimal(3,1) | NO | | NULL | |
+-------+--------------+------+-----+---------+-------+


I would like to count for every column starting with 'c' the number of values that are above a certain threshold (say 20.0).

On a single column (e.g. c0) I would do something like:

SELECT COUNT(*) FROM MYTABLE WHERE c0 > 20.0;


However, how do I do the same for c1, c2, c3, etc... in a single query?

Answer

I think this will work for you:

SELECT SUM(IF(c0 > 20, 1, 0)) AS count_0, SUM(IF(c1 > 20, 1, 0)) AS count_1
FROM table
Comments