Apr 9 2009
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.
Apr 9, 2009 at 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
Dominic O'Connor Says:
Apr 9, 2009 at 10:52 AM If you only have one condition, you can also use an IF clause<br /><br />SELECT<br /> Person.Name<br /> , SUM(IF(Property.Active = 1,1,0)) AS ActiveProperty<br /> , SUM(IF(Property.Active = 1,1,0)) AS InactiveProperty<br />FROM Person<br /> INNER JOIN Property ON Person.ID = Property.PersonID
Dominic O'Connor Says:
Apr 9, 2009 at 10:52 AM If you only have one condition, you can also use an IF clause<br /><br />SELECT<br /> Person.Name<br /> , SUM(IF(Property.Active = 1,1,0)) AS ActiveProperty<br /> , SUM(IF(Property.Active = 1,1,0)) AS InactiveProperty<br />FROM Person<br /> INNER JOIN Property ON Person.ID = Property.PersonID
Apr 9, 2009 at 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
Apr 9, 2009 at 8:41 PM Yep, I'll have to adjust my code accordingly :)
Apr 9, 2009 at 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.