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