SQL Counting with Conditions
Not sure why I never thought of this before (and I know I'm not the first to come to this conclusion judging by Google), but I just figured out how to count records in a query based on their value.
Let's assume that you have a person table, and a property table. Each person has an unlimited number of properties which are either active or inactive, and you need to figure out how many they have of each. I've typically done this by using subselects or derived queries, but this is heavy on the server and requires a lot of additional table locking when you're not careful.
So instead of doing something like this:
, Count(SELECT ID FROM Property WHERE PersonID = Person.ID AND Active = 1) AS ActiveProperty
, Count(SELECT ID FROM Property WHERE PersonID = Person.ID AND Active = 0) AS Inactive Property
You can do this:
, SUM(CASE WHEN Property.Active = 1 THEN 1 ELSE 0 END) AS ActiveProperty
, SUM(CASE WHEN Property.Active = 0 THEN 1 ELSE 0 END) AS InactiveProperty
INNER JOIN Property ON Person.ID = Property.PersonID
The second query is easier to read, and far easier on the database itself.
Posted by Daniel Short on Apr 9, 2009 at 9:57 AM | Categories: ColdFusion