looking for a slight variant of GROUP BY

I have a table like so:

id attr
1  A
2  A
3  C
4  C
5  D
6  F

I want a count of all the A's B's (but not the C's D's, etc..) Note that my table has zero B's.

So I want a command like this:

SELECT count(attr=A, attr=B) FROM table;

or this:

SELECT count(*) FROM table GROUP_BY attr IN (A, B);

and get:

attr count
A    2
B    0

My actual table has about a thousand attrs. I want to do a gro开发者_Go百科up_by-ish thing on maybe a hundred or so of them. It's important that i get the count of zero for certain attrs and that i can correlate the attr to the count.

I know this is a basic question and I'm not surprised if this has been asked before. I searched.. But my apologies anyway..

SELECT T.attr,
       COUNT(Y.id)
FROM   (SELECT 'A' AS attr
        UNION ALL
        SELECT 'B') AS T
       LEFT JOIN YourTable Y
         ON Y.attr = T.attr
GROUP  BY T.attr  

This should work for you.

SELECT T.Attr,Count(A.ID)
FROM (
SELECT CONVERT('A',char) AS Attr
UNION 
SELECT CONVERT('B',char)  AS Attr
) AS T
LEFT JOIN MyTable AS A
ON T.Attr=MyTable.Attr
GROUP BY T.Attr
ORDER BY T.Attr;

The Convert part may not be necessary but was necessary in my testing.