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.
Posted by Daniel Short on Apr 9, 2009 at 9:57 AM | Categories: ColdFusion -

4 Comments

Dominic O'Connor

Dominic O'Connor wrote on 04/09/09 10:52 AM

If you only have one condition, you can also use an IF clause SELECT Person.Name , SUM(IF(Property.Active = 1,1,0)) AS ActiveProperty , SUM(IF(Property.Active = 1,1,0)) AS InactiveProperty FROM Person INNER JOIN Property ON Person.ID = Property.PersonID
Nick

Nick wrote on 04/09/09 4:07 PM

this is easier and the fastest. select person.name, property.active, count(*) from FROM Person INNER JOIN Property ON Person.ID = Property.PersonID group by person.name, property.active
Daniel Short

Daniel Short wrote on 04/09/09 8:41 PM

Yep, I'll have to adjust my code accordingly :)
Daniel Short

Daniel Short wrote on 04/09/09 8:43 PM

And that's fine if you're okay with working with multiple rows per parent record. However, that makes it difficult to use through an API. You'd have to perform a pivot of some sort to move the two rows into two columns instead.