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

For More MySQL Tips

Check out these other posts: