SQL Counting Multiple Columns With Criteria

When doing counts, sometimes you want to count a record only when it meets a certain criteria. This can be annoying because the criteria generally needs to be posted in the "where" clause. This will give you only one count per sql query. If you want to have multiple counts per sql query you need to do sub-queries that can be costly.

select (Select count(FavoriteCandy) from mytable where FavoriteCandy='Tootsie Rolls') as TootsieCount, (Select count(FavoriteCandy) from mytable where FavoriteCandy='Baby Ruth') as BabyRuthCount

While this works it is really expensive. If multiple different counts over a large number of records is required, this method is not appropriate. While parusing the vast internets I ran into a different way of accomplishing the same task. I much prefer it to the former method.

select SUM(CASE when FavoriteCandy='Tootsie Rolls' THEN 1 END) as TootsieCount,SUM(CASE when FavoriteCandy='Baby Ruth' THEN 1 END) as BabyRuthCount from mytable

This treats the successful records as the number one and then sums the number of successes. When trying to count a number of different columns with criteria, this is definitly a cleaner and faster way to make the magic happen.