Make homepage|Add to favorites
 

MYSQL TOOLS
reviews, articles, tips & snippets

 
Buy SQL » MySQL » How to select SUM or COUNT of rows with different condition

How to select SUM or COUNT of rows with different condition

Author: Darek Date: 21-01-2014, 20:18

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 |
+--------+----+----+----+

Tags: sum, count, select, where, if

Dear visitor, you went to the site as unregistered user.
We recommend you to Register or Sign In.

Comments:

Leave a comment
  • Group: Users
  • ICQ: 227135166
  • Registration: 20.07.2015
  • Comments: 1
  • Publications: 0
^
Я даже видео видел по этой теме. Сейчас скину ссылку, если найду.
Information
Members of Guests cannot leave comments.