TSQL: Count / SUM with 'Where' condition

My problem

Doing some reporting services I found myself in need to have group aggregates. I try to solve most hurdles in the SQL queries so I don't need to much trickery in the report definitions. I've done these queries many times and usually found myself doing sub queries from the main one, or using temporary tables etc.

Imagine this question based on the tables below: How many herbivores with what total value are in my zoo?



Example tables :)

Problem solved!

I've found the answer on Stack Overflow. The trick is using a 'join' and a 'case when' in your count/sum syntax. So the query to answer the question above is like this:

 SELECT dbo.Food.[Description] as Food  
     ,COUNT(CASE dbo.Species.FoodID WHEN dbo.Food.Id THEN 1 ELSE null END) AS [Count]  
     ,SUM((CASE dbo.Species.FoodID WHEN dbo.Food.Id THEN dbo.Species.Value ELSE null END) AS Value  
  FROM dbo.Species  
  LEFT JOIN dbo.Food ON dbo.Species.FoodID = dbo.Food.Id 
  GROUP BY dbo.Food.[Description]  
 GO  

And the result:

The expected result of the query