Query for Counting column with Datatype Bit

I have got a column name check , with a datatype bit(true or False), Wha开发者_JAVA技巧t I want to do is count how many false and true are there in the column .

SELECT 
COUNT(FeatureState) AS tot_true, 
COUNT(*)-COUNT(FeatureState) AS tot_false
FROM productDetail
 WHERE FeatureState= 1

This works

---

How about;

SELECT 
  COUNT(CASE fld WHEN 0 THEN 1 ELSE NULL END) AS ZEROS,
  COUNT(CASE fld WHEN 1 THEN 1 ELSE NULL END) AS ONES
FROM
  T

--alternative

SELECT 
  SUM(CASE fld WHEN 0 THEN 1 ELSE 0 END) AS ZEROS,
  SUM(CASE fld WHEN 1 THEN 1 ELSE 0 END) AS ONES
FROM
  T

---

Try this, should work:

SELECT 
    COUNT(field) AS tot_true, 
    COUNT(*)-COUNT(field) AS tot_false
FROM table
WHERE field=1

---

I've used SIGN to change bit to int

SELECT 
    SUM(SIGN(field)) AS tot_true, 
    SUM(1-SIGN(field)) AS tot_false
FROM table

Or use the fact that COUNT ignores NULLs

SELECT 
    COUNT(NULLIF(field, 0)) AS tot_true, 
    COUNT(NULLIF(field, 1)) AS tot_false
FROM table

This works if you want other values MAX(SomeOtherField) where the highest values comes from the "false" rows filtered out in other solutions

---

SELECT 
SUM(CASE WHEN FeatureState = 1     THEN 1 ELSE 0 END) AS TrueCount,
SUM(CASE WHEN FeatureState = 0     THEN 1 ELSE 0 END) AS FalseCount,
SUM(CASE WHEN FeatureState IS NULL THEN 1 ELSE 0 END) AS NullCount,
COUNT(1)                                              AS TotalCount
FROM ProductDetail