When selecting count, sum, or some other aggregate function, the value isn’t determined until after the WHERE clause so a condition can’t be placed there. There is still syntax for specifying a condition for this, it is to use HAVING.

The following example is to count all of the logs an active user has made only if they have more than two logs.

SELECT users.user_id, count(logs.log_id) AS count
FROM users
JOIN logs
ON users.user_id = logs.user_id
WHERE user.deleted = 0
GROUP BY user.user_id
HAVING count > 2

It works the same for sum. Example shows users who have logged less than an hour of time.

SELECT users.user_id, sum(logs.log_time) AS sum
FROM users
JOIN logs
ON users.user_id = logs.user_id
WHERE user.deleted = 0
GROUP BY user.user_id
HAVING sum < 60

Common aggregate functions this works with:

  • AVG
  • COUNT
  • MAX
  • MIN
  • STD
  • SUM

Related posts:

  1. Setting up Apache2, mod_python, MySQL, and Django on Debian Lenny or Ubuntu Hardy Heron  Both Debian and Ubuntu make it really simple to get a server up and running. I was trying a few...
  2. Careful with Numeric Data Types in MySQL  TINYINT can be set to length of 4, but that doesn’t mean the max value is 9999. A tiny int...
  3. Tips for MySQL to PostgreSQL Switch  If you’ve decided to move a few tables from MySQL to PostgreSQL, these few tips might help. I won’t get...