How to Count the number of Countries From registered users in my users table

I have two tables country and users. The country holds id's for every country in the world. When a user registers into the system the id of country is saved in a column named country as a foreign key.

Now what I want to do is to count how many countries users registered, for instance 3 users registered from the United States, It would show the name of the country and how many users registered from that country like this United States : 3 and so on, only for the countries that users registered from.

Here is my Schema:

users table

country table:

Here is what I tried however I am not able to understand how to make it work properly:

<?php
$query = $conn->prepare("SELECT users.id 
AS userid, users.country AS country, 
country.id AS countryID, country.country_name AS countryName 
    FROM users 
    LEFT JOIN country 
    ON country.id = users.country 
    WHERE users.name is not null;");
    $query->execute();

    while($row = $query->fetch()){
      $countriesID = $row["countryID"];
      $countryNames = $row["countryName"];
   
      if($row["country"] == $countriesID){
        $totalCountryq = $conn->prepare("SELECT COUNT(users.country) as c  FROM users LEFT JOIN country ON users.country = country.id;");
        $totalCountryq->execute();
        if($row2 = $totalCountryq->fetchAll()){
          $total = $row2['c'];
          print_r($total);
     
        }
      }
      //echo $countriesID;
    }

?>

Answer

SELECT c.id, c.country_name, count(u.country) AS country_count

FROM counrty AS c

JOIN users AS u ON u.country = c.id

GROUP BY c.id

ORDER BY country_count DESC

Advertisement

Answer