How to get count of two values from a single table

[faisal.qureshi.7121614]

Hi ! I need to have a single mysql query which should get COUNT of two different values in a single field, suppose i have a field in a table 'approved', and there are only two values ie. approved=0 or approved=1, and have suppose 100 rows for them,so how to get them counted like 0=20, 1=80, i have used distinct values query generated by PhpMyAdmin but i am not satisfied with it.
Thanks in advance !

[cereal]

Use group by and count, for example:

select count(approved) from pma where approved in(0,1) group by approved;

live: http://sqlfiddle.com/#!2/f52df/1

[faisal.qureshi.7121614]

Hi cereal, thanks for your help, your query seems perfect but now i am having problem how to show results using php's while loop, is there anything i need to change in query when using in php code? as its not showing me result when i run my php file.i have code:

$sql_count = mysql_query("SELECT count(approved) FROM `business_details` WHERE approved IN(0,1) GROUP BY approved");
    while ($rows = mysql_fetch_array($sql_count)){
        echo $rows['approved'];
            }

it says Undefined index :approved on line 4
please help , Thanks!

[jstfsklh211]

the field name is currently "count(approved)" if you dumped your results thats what you would see

count(approved) as approved would return approved as a field

[faisal.qureshi.7121614]

yes jstfsklh211 you are right,thanks, i did it as you suggested, it worked but only showing result which has values=1, (inside my while loop) its still missing counts for 0 values.Any idea?

[cereal]

@faisal

I get both rows, probably you see them on the same line, try to add a separator, for example <br />:

while ($rows = mysql_fetch_array($sql_count))
{
    echo $rows['approved'] . "<br />";
}

Note: the result set is sorted automatically by the IN() function, so it does not matter if you write IN(0,1) or IN(1,0), in both cases will sort 0,1.

Docs: http://dev.mysql.com/doc/refman/5.5/en/comparison-operators.html#function_in

[faisal.qureshi.7121614]

@cereal
Your queries are true, but probably i asked my question wrongly or you guys misunderstood it, i mean to have counts of sibgle value in field either 0 or 1 (your query is for multiple entries in a single row under a filed, which is not my issue), let me explain you with an image:

i need to count only two possible and allowed values 0 and 1, in approved filed, from the start of table to End using while loop to display them, again keep in mind that the filed doesn't have multiple entries for a single record(row).
Thanks again !

[ehpratah]

Hi you can use a condition statement inside your query that check whether your row contain the data that you wanna count..

SELECT
  COUNT(DISTINCT CASE WHEN approved = '0' THEN  id END) 'zero',  
  COUNT(DISTINCT CASE WHEN approved = '1' THEN  id END) 'ones'
FROM business_details

[catalinetu]

hi, you can try this

SELECT SUM(approved) `ones`, COUNT(*)-SUM(approved) 'zero' FROM `table`

[faisal.qureshi.7121614]

Thank you both ehpratah and catalinetu, your queries worked for my case very well, UP vote for you both ! have a great day !