Im trying to get a query correct where I can display the data by State with each of the States cities with stores in it with the COUNT (Number of stores in each city). Any help is greatly appreciated.

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

Right now I have below which does not give me the counts in each city.

$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>";

1 Answers

2
Tim Biegeleisen On

I can suggest the following GROUP BY query:

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