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
精彩评论