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

I use webfaction to host a lot of my django projects. It has an easy setup that will get you developing quickly and a great community of talented programmers. There is also a quick setup for rails, wordpress, and a lot more.

Related posts:

  1. How To Use Triggers to Track Changes in MySQL  Setting constraints and rules in the database is better than writing special code to handle the same task since it...
  2. How to Break a MySQL Left Join  Care must be taken when placing conditions on the results of the right-hand table of a LEFT JOIN because it...
  3. MySQL Fun  Trying to find a way to update a bunch of records in the request table by changing request_taken_time to a...