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?

``````SELECT SUM(IF(c0 > 20, 1, 0)) AS count_0, SUM(IF(c1 > 20, 1, 0)) AS count_1