This is the query that just got me to thinking that I should blog about mySQL hints, tips, and ramblings. So... thinking turned into action and now I have a blog! The next step is to be able to keep it going so that it becomes useful to mySQL database administrators that are concerned about performance of mySQL and also need make sure that queries are written well.
Now... onward and forward...
The Challenge
Return results based on the sum of a column and user_id, where the sum of this column is greater than 10 for each user_id.
The Setup
Table: sample_table
Columns: user_id bigint not null auto_increment, date_added datetime not null, int_aggregate_column int not null
The Expected Finish Line
Result Format: user_id, SUM(int_aggregate_column)
Criteria: where SUM(int_aggregate_column) is greater than 10
The Challenge
The HAVING statement is powerful and underutilized. I cannot count the number of times I have seen php loops when a HAVING statement will take care of the issue. Mind you, there are times when having a php loop statement is better, but that is for another post.
We knew we would use the HAVING statement, but how?
The Answer
SELECT DISTINCT user_id, SUM(int_aggregate_column) AS sum FROM sample_table WHERE date_added >='2007-06-01' AND date_added < '2007-07-01' GROUP BY user_id HAVING sum > 10;
Fun, eh? (That' s my Canadian accent coming through)
As a test, you can limit the records returned to ensure that you are indeed getting the results you want.
