SQL Server : zero values with COUNT(*)

I have a table @MemberAttribute

MemberID AttributeID AttributeValue
1            1         False 
1            2         True 
2            1         False
2            2         True 
3            1         False
3            2         False

I want to group by attributeID and get count of attributes whose value is True. But when attributetype is false for a particular attribute there I want it to display 0. Right now the attributeID with all false vallues just doesn't show up. Here is the sql query

SELECT MA.AttributeID, GA.Name,
--COUNT(isNull(MA.AttributeID,0)) as AttributCount,
CASE WHEN COUNT(MA.AttributeID) > 0 THEN COUNT(MA.AttributeID) Else 0 END AS 'AttributCount'
--CASE WHEN COUNT(MA.Attribut开发者_JAVA技巧eID) < 0 THEN 0 Else COUNT(MA.AttributeID) END AS 'TOTAL Attributes'
from GroupAttribute GA
inner join   @MemberAttribute MA on GA.GroupAttributeID = MA.AttributeID
WHERE MA.AttributeValue = 'True'
GROUP BY MA.AttributeID,GA.Name

FOR AttributeID = 1 all the values are = False... so the result is like this

AttributeID      Name      AttributeCount <br/>
2                Attr2        2 <br/>

I want

1                 Attr1      0  <br/>

too in the result set.

---

Try this - note that the 1 in ...THEN 1 ELSE ... is an arbitrary non-NULL value - it could be 'fred' or 12345 - that it's not NULL is the important part.

SELECT MA.AttributeID, GA.Name,
COUNT(CASE WHEN MA.AttributeValue = 'True' THEN 1 ELSE NULL END) AS 'AttributeCount'
from GroupAttribute GA
inner join   @MemberAttribute MA on GA.GroupAttributeID = MA.AttributeID
GROUP BY MA.AttributeID,GA.Name

...somewhat more intuitively (thanks Ken) - and note that here the 1 and 0 are important...

SELECT MA.AttributeID, GA.Name,
SUM(CASE WHEN MA.AttributeValue = 'True' THEN 1 ELSE 0 END) AS 'AttributeCount'
from GroupAttribute GA
inner join   @MemberAttribute MA on GA.GroupAttributeID = MA.AttributeID
GROUP BY MA.AttributeID,GA.Name

---

After some wriggling, I came up with this beauty containing no CASE expression:

SELECT GA.GroupAttributeID AS AttributeID, GA.Name, 
       COUNT(MA.AttributeID) AS AttributeCount
FROM GroupAttribute AS GA 
LEFT OUTER JOIN @MemberAttribute AS MA 
  ON GA.GroupAttributeID = MA.AttributeID AND MA.AttributeValue = 'True'
GROUP BY GA.GroupAttributeID, GA.Name

This takes advantage of the fact that if there are no 'True' values for a particular AttributeID, the MA.AttributeID resulting from the LEFT OUTER JOIN will be NULL. The NULL value passed into COUNT() will lead to an AttributeCount of zero. The LEFT OUTER JOIN also ensures that a row will be present in the result set for AttributeID rows with zero counts.

The assumption with this query is that all group attributes are represented in the @MemberAttribute table variable. If not, there will be rows with zero counts representing those group attributes that are absent. If this is undesirable, a WHERE clause can be added to filter them out, complicating this query. Will's solution(s) would be far more practical if this is the case.

The execution plan compares well with Will's first solution, containing one less (Compute Scalar) step. It does use a LEFT OUTER JOIN vs an INNER JOIN, however, making the two methods practically identical for this simple example. It would be interesting to see how the two solutions scale if table variable is converted to a fairly large table, instead.

Will's actual plan for his solution involving COUNT():

My actual plan: