SQL Select COUNT (Value = Value)?

Question

I want to count all rows that have only the value that I want:

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 fine without the“ = ". How can I count the lines that Burned = 1 in them and Burned = 0 in them?

Thanks Dan

Answer 1

Use the 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 considers NOT NULL values, so this will give you the desired result.