Viewing by month: April 2009

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: SELECT Person.Name , 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 FROM Person You can do this: SELECT Person.Name , 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 FROM Person INNER JOIN Property ON Person.ID = Property.PersonID The second query is easier to read, and far easier on the database itself.
4 comments | Posted by Daniel Short on Apr 9, 2009 at 9:57 AM | Categories: ColdFusion -