php - 具有 COUNT 和 ORDER BY 以及隐藏结果的 MySQL 查询

Question

我试图得到一个正确的查询，我可以按州显示数据，每个州的城市都有商店，并带有 COUNT(每个城市的商店数量)。非常感谢任何帮助。

California
Los Angeles (5)
San Diego (8)
San Francisco (3)
...

现在我的数据如下，并没有给出每个城市的计数。

$sql = "SELECT DISTINCT city, state FROM locations  WHERE open = 'Y' ORDER BY state ASC";
$result = mysqli_query($conn,$sql);
$num_columns = 1;
$rows = array();
while($row = mysqli_fetch_assoc($result)){
    if(!isset($rows[$row['state']])){
        $rows[$row['state']] = array();
    }
    $rows[$row['state']][] = $row['city'];
}
echo "<table>";
foreach($rows as $state => $cities){
    echo '<tr><th colspan="'. $num_columns .'">'. $state .'</th></tr>';
    $cityData = array_chunk ($cities, $num_columns); 
    sort($cityData);  // split array into chunk of $num_columns cities per array
    foreach($cityData as $row){
        echo "<tr>";
        for($i=0; $i<$num_columns; $i++){
            $city = isset($row[$i]) ? $row[$i] : "";
            echo "<td>$city</td>";
        }
        echo "</tr>";
    }
}
echo "</table>";

Answer 1

我可以建议以下GROUP BY查询:

SELECT
    city,
    state,
    COUNT(CASE WHEN open = 'Y' THEN 1 END) AS cnt
FROM locations
GROUP BY
    city,
    state
ORDER BY
    state;