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:
- 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...
- 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...
- MySQL Fun Trying to find a way to update a bunch of records in the request table by changing request_taken_time to a...