Count() to show all records

[inbowns]

I was able to get my count to work that I posted in my previous post but I have a problem I would like the post to count everything but if there's a that is Yes in my column but if there all No I would also like it to display 0 in the same output something like this.

 

Type                        Response = Yes

Car                                5                 

Truck                              1

Bike                                0

Walk                              3

Swim                              0

Flying                              1

 

 

So the "0" should show that the database saw "No" and displayed a zero.

[Pikachu2000]

I don't follow.

[Pandemikk]

Uhm I think I do... http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

 

SELECT IF(response = 'Yes', COUNT(whatever you're counting), 0)

[inbowns]

Uhm I think I do... http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

 

SELECT IF(response = 'Yes', COUNT(whatever you're counting), 0)

 

Thank you for your response I tried this it didn't work is there something I'm missing something? Do i need JOIN  or UNION to make this work?

[Pikachu2000]

You need to better explain just what you're trying to accomplish. Some sample data, a table structure, and expected results would be helpful.

[fenway]

You need SUM and IF:

 

SUM( IF(expr,1,0) )

[inbowns]

You need SUM and IF:

 

SUM( IF(expr,1,0) )

 

I guess this will work but i also need it to show if there's for example 3 "no" then the data it should return a zero but currently it doesn't show anything. Essentially i want a count of the "Yes" if there 10 "yes" i want that actual count and if there's a "No" it should give me a zero regardless of how many there are listed.

 

I hope this is clear

[awjudd]

SELECT Type, COUNT ( CASE Response WHEN 'Yes' THEN 1 ELSE NULL END ) AS ResponseCount
FROM table

 

~awjudd

[fenway]

It's all the same.