Author: Steven Neiland
Published:

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.

Using HAVING

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.

Related Blog Postings

What Do You Think?

Reader Comments

Commenting has been disabled.

Archives Blog Listing

Tag Listing

Learn CF In A Week

Treehouse

 
Fork me on GitHub