Warning: This blog entry was written two or more years ago. Therefore, it may contain broken links, out-dated or misleading content, or information that is just plain wrong. Please read on with caution.
Sometimes I am amazed at how little some developers know when it comes to writing SQL. With the advent of ORM I suspect the problem is only going to get worse but I digress.
Take for example this problem I came across in a real application recently:
(I have simplified the code for clarity)
Problem: Aggregate and filter by sales totals
Example: List all departments along with their sales totals that have done more than $1000 in sales excluding the software development department.
In looking at the source code I saw that the developer had tried all the classic approaches.
Attempt 1: Fail
Obviously this does not work
SELECT departmentName , SUM(amount) AS totalSales FROM sales WHERE departmentName != 'development' AND totalSales > 1000 GROUP BY departmentName
Attempt 2: Fail
Likewise this did not work
SELECT departmentName , SUM(amount) AS totalSales FROM sales WHERE departmentName != 'development' AND SUM(amount) > 1000 GROUP BY departmentName
Attempt 3: Success
Finally they hit on this solution that worked and left it at that.
SELECT x1.* FROM ( SELECT departmentName , SUM(amount) AS totalSales FROM sales WHERE departmentName != 'development' GROUP BY departmentName ) AS x1 WHERE x1.totalSales > 1000
While this does indeed work I really dislike wrapping the SELECT in another one just to filter on an aggregate column. Fortunately there is a better way.
The better way to achieve this is to use the "HAVING" clause in place of the "WHERE" clause for filtering aggregate columns (You still use the "WHERE" clause to filter non aggregates). Use by placing after the "GROUP BY" clause like this:
SELECT departmentName , SUM(amount) AS "Total sales" FROM sales WHERE departmentName != 'development' GROUP BY departmentName HAVING SUM(amount) > 1000
In terms of performance there was no measurable difference between the two on this database but the code is immediately more readable and maintainable.