SQL Select COUNT(Value=Value) Possible?

Question

I want to Count all the rows that only have the value I want like this:

SELECT Users.Balance,
       Users.FreeBids,
       COUNT(Bids.Burned = 0) AS 'ActiveBids',
       COUNT(Bids.Burned = 1) AS 'BurnedBids'
FROM   Users
       INNER JOIN Bids
         ON Users.ID = Bids.BidderID
WHERE  Users.ID = 2
GROUP  BY Users.Balance,
          Users.FreeBids  

It says "Invalid Syntax Neat '=' It works perfectly without the '='. How can I count the rows that Burned=1 in them and Burned=0 in them?

Thanks, Dan

Answer 1

Use a CASE statement

COUNT(CASE WHEN Bids.Burned=0 THEN 1 END) AS 'ActiveBids', 
COUNT(CASE WHEN Bids.Burned=1 THEN 1 END) AS 'BurnedBids'

There is an implicit ELSE NULL. COUNT only counts NOT NULL values so this will give you the result you need.