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:
精彩评论