How to get count in this query?

I have one table with following columns:

ListID----           Status

开发者_如何学Go1---------             YES

2---------             YES

3----------             NO 

I want to write an SQL statement which will return count for each ListID column having Status as "YES".

How do I create this SQL statement, so that I get ListID 3 in it with Count as 0?

Please let me know.

---

Use MySQL IF-function:

If the Status is YES, count the field. Else don't count it (0).

SELECT ListID, SUM(IF(Status="YES",1,0)) 
FROM yourtable
GROUP BY `ListID`

Checkout this previous answer: Get paid cash amount grouped by day

---

TRY THIS

SELECT ListID, SUM(decode(Status,'YES',1,0))  
FROM yourtable 
GROUP BY ListID

---

You can use this very trick:

say YES is 1 and NO is 0 then just SUM(status) group by listId.

---

For this is enough SQL92 statement:

SELECT ListId, COUNT(*)
  FROM table
 GROUP BY ListId, Status
HAVING Status = 'Yes'

---

select status, count(ListID) 
from tablename
group by status