CODEWITHSUNDEEP
phpsqlpdo
Lewis
Lewis

Reputation: 300

group result by country and count

I have the following code;

$uid = '21';
$select = $dbh->prepare("SELECT * FROM `reports` WHERE `profileuid` = ?");
$select->bindParam(1,$uid, PDO::PARAM_STR);
$select->execute();

foreach($select as $row){

    $country = $row['country'];
    echo  $country;
}

and the following database structure;

enter image description here

Currently the above code gives this output

United Kingdom
United Kingdom
United States

My desired output

United Kingdom 2
United States 1

Thanks for any help; I would attempt this myself but I don't know how to do a count like this. Could this possibly be a GROUP?

Upvotes: 1

Views: 116

Answers (1)

DineshDB
DineshDB

Reputation: 6193

You can rewrite the query to:

SELECT Location, COUNT(1) as NoOfEntries  
FROM `reports` WHERE `profileuid` = ? 
GROUP BY Location
ORDER BY COUNT(1) DESC

It will group the country and give the count.

Upvotes: 1

Related Questions