count rows in mysql with php with specific value

Question

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";

$result = mysql_query("SELECT Zone FROM Tb1 WHERE Pressure = 'Yes'"); $num_rows = mysql_num_rows($result); echo $num_rows . " \n"; — user3283373
– user3283373, Commented Feb 18, 2014 at 13:27
but this is giving all total row in all zone not in particular zone type — user3283373
– user3283373, Commented Feb 18, 2014 at 13:28
@user3283373 edit your answer rather than putting these in the comments. — J2D8T
– J2D8T, Commented Feb 18, 2014 at 13:31
Stop using mysql_query. And stop respecting whoever told you to use it in the first place; it's been deprecated for a while now. Look into mysqli or PDO. — cHao
– cHao, Commented Feb 18, 2014 at 13:33

O. Jones · Accepted Answer · 2014-02-18 13:31:39Z

1

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

answered Feb 18, 2014 at 13:31

O. Jones

110k17 gold badges134 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Strawberry · Accepted Answer · 2014-02-18 13:33:43Z

1

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

answered Feb 18, 2014 at 13:33

Strawberry

34k14 gold badges43 silver badges57 bronze badges

2rs2ts · Accepted Answer · 2014-02-18 14:09:27Z

1

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

2rs2ts

11.1k12 gold badges56 silver badges100 bronze badges

answered Feb 18, 2014 at 13:44

birju

112 bronze badges

Abhik Chakraborty · Accepted Answer · 2014-02-18 13:30:59Z

0

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

answered Feb 18, 2014 at 13:30

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges