Conditions on Count or Sum in MySQL
Posted on July 22nd, 2008 by Greg Allard in MySQL |
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:
Add New Comment
Viewing 3 Comments
Thanks. Your comment is awaiting approval by a moderator.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
Do you already have an account? Log in and claim this comment.
myself suresh...a great programmer...if anybody needs help..just mail to me.
Add New Comment
Trackbacks