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