phpmysqlaggregate-functions

SQL statement not calculating the SUM with multiple WHERE clauses

I am trying to return the total of two columns based on the locations associated to each of the two columns. When I use the OR statement like such:

$query = "
    SELECT
        ec.ElecEnergy,
        ec.GasEnergy,
        ua.City, 
        SUM(ec.ElecEnergy) AS ecTotal,
        SUM(ec.GasEnergy) AS gcTotal
    FROM energyconsumption ec
    INNER JOIN useraccount ua ON ec.User = ua.id
    WHERE
        ua.City ='London'
        OR City ='Manchester'
        OR City ='Luton'
        OR City ='Newcastle'
        OR City ='Birmingham'
        OR City ='Blackburn'
    GROUP BY ec.ElecEnergy, ec.GasEnergy, ua.City
    ORDER BY ec.ID ASC
";

$result = mysqli_query($conn,$query);
$r = array();
if ($result->num_rows) {
    while ($row = mysqli_fetch_array($result)) {
        array_push($r, array(
            'ElecConsump' => $row['ecTotal'],
            'GasConsump' => $row['gcTotal'],
            'Location' => $row['City']
        ));
    }
}
echo json_encode(array('results' => $r));

It just returns everything from my database. What I want is a sum of the columns based for each of the cities I have listed.

I'm not sure what I'm doing wrong.

Solution

  • You only want to include city in the group by, rather than the columns you are aggregating. Remember, the columns in the group by define the rows that are being returned, with unique combinations on each row.

    Also, use in . . . much simpler to write and read:

    SELECT ua.City, SUM(ec.ElecEnergy) AS ecTotal, SUM(ec.GasEnergy) AS gcTotal
FROM energyconsumption ec INNER JOIN
     useraccount ua
     ON ec.User = ua.id
WHERE ua.City IN ('London', 'Manchester', 'Luton', 'Newcastle', 'Birmingham', 'Blackburn')
GROUP BY ua.City
ORDER BY ua.City ASC;

    Notice that I also changed the ORDER BY clause -- assuming you are returning one row per city.

    If you want the totals for all the cities combined, then: