Tuesday, 2 June 2015

Mysql: How to select SUM or COUNT of rows with different condition

Suppose we have this table
+--------+------+------+------+
| user   | c1   | c2   | c3   |
+--------+------+------+------+
| Carl   |   10 |   10 |   21 |
| Isabel |    5 |    1 |    7 |
| Isabel |    0 |   12 |    8 |
| Isabel |    3 |   17 |   15 |
| Carl   |    8 |   30 |    1 |
+--------+------+------+------+
 
Now we need to SUM all values of each column per user if the value is greater then 5
SELECT
  user,
  SUM(IF(c1 > 5, c1, 0)) c1,
  SUM(IF(c2 > 5, c2, 0)) c2,
  SUM(IF(c3 > 5, c3, 0)) c3
FROM
  table1
GROUP BY
  user;
+--------+------+------+------+
| user   | c1   | c2   | c3   |
+--------+------+------+------+
| Carl   |   18 |   40 |   21 |
| Isabel |    0 |   29 |   30 |
+--------+------+------+------+
 
The similar query when we need to COUNT rows
SELECT
  user,
  COUNT(IF(c1 > 5, c1, NULL)) c1,
  COUNT(IF(c2 > 5, c2, NULL)) c2,
  COUNT(IF(c3 > 5, c3, NULL)) c3
FROM
  table1
GROUP BY
  user;
+--------+----+----+----+
| user   | c1 | c2 | c3 |
+--------+----+----+----+
| Carl   |  2 |  2 |  1 |
| Isabel |  0 |  2 |  3 |
+--------+----+----+----+

0 comments:

Post a Comment