0

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 !

5
Contributors
9
Replies
67
Views
3 Years
Discussion Span
Last Post by faisal.qureshi.7121614
0

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!

1

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

0

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?

0

@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:
aae37d0bd928f96ca26714a8ceb91c70

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 !

1

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
1

hi, you can try this

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

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.