How can the GROUP BY clause be used in MySQL queries to aggregate data for PHP applications?

The GROUP BY clause in MySQL queries is used to group rows that have the same values in specified columns. This is useful for aggregating data, such as counting the number of rows that have the same value in a specific column. In PHP applications, the GROUP BY clause can be used in conjunction with aggregate functions like COUNT, SUM, AVG, etc., to calculate and display summarized data. 

<?php
// Connect to the database
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database";

$conn = new mysqli($servername, $username, $password, $dbname);

// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// Query to get the count of rows grouped by a specific column
$sql = "SELECT column_name, COUNT(*) as count FROM table_name GROUP BY column_name";
$result = $conn->query($sql);

// Display the results
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo "Column: " . $row["column_name"]. " - Count: " . $row["count"]. "<br>";
    }
} else {
    echo "0 results";
}

$conn->close();
?>

Keywords

MySQL GROUP BY clause aggregation PHP applications data extraction

Related Questions