SQL Select Most Common Record Occurrence List

Learn the SQL syntax of a very useful database query. This single query is full of logic that you can carry into other queries you need to write. Learn to group occurrences, count, order and limit the result set. It is useful for finding the most popular occurrences in your databases and rendering a list of them.

<?php
// Connect to your database
$db_conx = mysqli_connect("localhost", "db_user", "db_password", "db_name");
if (!$db_conx) { die( mysqli_connect_error() ); }
// Build the list from a query result-set array
$list = "";
$num = 0;
$sql = "SELECT country, count(country) AS amount FROM nerds GROUP BY country ORDER BY amount DESC LIMIT 12";
$query = mysqli_query($db_conx, $sql) or die( mysqli_error($db_conx) );
while($row = mysqli_fetch_array($query, MYSQLI_ASSOC)){
    $num++;
    $country = $row["country"];
    $amount = $row["amount"];
    $list .= $num.') '.$country.' - <b>'.$amount.'</b> people<br>';
}
mysqli_close($db_conx);
echo $list;
?>

SELECT col1, count(col1) AS amount FROM table_name GROUP BY col1 ORDER BY amount DESC LIMIT 12