CODEWITHSUNDEEP
phpmysql
user2950355
user2950355

Reputation: 45

How to calculate total number of entries per state using mysql and php

I have a table "data" with a column "name" "state" and a few more

name     state
peter     MN
john      NY
jay       NY
sam       CO
jack      TX
jill      NO

I want to calculate the number of entries per state and want my output as follows for example:

NY: 125
MN: 21
CO: 17
TX: 10
NO: 59

etc...

I have a query like this

$stmt = $db->query("SELECT state, COUNT(*) FROM `data` GROUP BY state;");
$nums = $stmt->rowCount();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr>
       <td>" . $row["state"] . "</td>
       <td>$nums</td>
     </tr>";
}

This displays every state in my table but does not return the corresponding number of entries for that state. This only returns the number of states i.e. 50. How can I display the number of entries per state?

Upvotes: 0

Views: 156

Answers (2)

ka_lin
ka_lin

Reputation: 9442

$stmt = $db->query("SELECT COUNT(name) as occurances,state FROM `data` GROUP BY state;");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
echo "<tr>
       <td>" . $row["occurances"] . "</td>
       <td>" . $row["state"] . "</td>
     </tr>";
}

Try this version,select the names only

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43464

You seem not to be referring to the column which has the count. Try aliasing it an referencing the alias in your PHP code:

$stmt = $db->query("SELECT state, COUNT(*) cnt FROM `data` GROUP BY state;");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    echo "<tr>
       <td>" . $row["state"] . ":</td>
       <td>" . $row["cnt"] . "</td>
     </tr>";
}

Upvotes: 1

Related Questions