CODEWITHSUNDEEP
phpmysqldatabasecount
user3283373
user3283373

Reputation: 69

count rows in mysql with php with specific value

I have a table name is tb1

tb1
id zone  pressure
1  India  Yes
2   USA   No
3   UK    Yes
4  India  Yes
5  AUS    No
6   UK    Yes

Pressure have two kind of entry like "yes" and "no" i need result in this manner 

Zone   Pressure
        Yes  No
India    2   0
USA      0   1
UK       1   1
AUS      0   1

My effort so far...

$result = mysql_query("SELECT Zone FROM Tb1 WHERE Pressure = 'Yes'");
$num_rows = mysql_num_rows($result); echo $num_rows . " \n";

Upvotes: 0

Views: 1453

Answers (4)

birju
birju

Reputation: 11

SELECT Zone,
       SUM(pressure='Yes') as `Yes`,
       SUM(pressure='No') as `No`
FROM Tb1 
GROUP BY Zone;

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

SELECT zone
     , SUM(pressure='yes') yes
     , SUM(pressure='no') no 
  FROM my_table 
 GROUP 
    BY zone;

Upvotes: 1

O. Jones
O. Jones

Reputation: 108641

SELECT zone,
       SUM(pressure = 'Yes') AS `Yes`,
       SUM(pressure = 'No') AS `No`
  FROM tb1
 GROUP BY zone

does the trick you need. It's an aggregate query. Here's a fiddle. http://sqlfiddle.com/#!2/54889/2/0

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

You can do as

select tb1.zone,
sum(tb1.pressure = 'Yes') as Yes,
sum(tb1.pressure = 'No') as No
from tb1
group by tb1.zone

Upvotes: 0

Related Questions