PHP MYSQL PDO SUM of columns

Question

I'm new to php and I've searched for the past hour and read all the documentation I could find and nothing is helping. I have a table that has a bunch of rows of data. I'm trying to pick one column from the whole table and add them all together. Here is what I got. All this tells me is how many rows there are that match my query, not the total sum of column I want. Any help is appreciated.

$res1 = $db->prepare('SELECT sum(distance) FROM trip_logs WHERE user_id = '. $user_id .' AND status = "2"');
$res1->execute();
$sum_miles = 0;
while($row1 = $res1->fetch(PDO::FETCH_ASSOC)) {
$sum_miles += $row1['distance'];
}
echo $sum_miles;

Answer 1

You're only returning one row in this instance. Modify your summed column to have an alias:

SELECT SUM(distance) AS totDistance FROM trip_logs ....

Now you can can fetch the row -

$row = $res1->fetch(PDO::FETCH_ASSOC);
echo $row['totDistance'];

No need to loop.

Answer 2

You can use SUM() without explicitely grouping your rows because if you use a group function in a statement containing no GROUP BY clause, it is equivalent to grouping on all rows.

If however you want to use the SUM() function for something slightly more complicated you have to group your rows so that the sum can operate on what you want.

If you want to get multiple sums in a single statement, for example to get the distance for all users at once, you need to group the rows explicitely:

$res1 = $db->prepare("
    SELECT
        SUM(distance) AS distance,
        user_id
    FROM trip_logs WHERE status = '2'
    GROUP BY user_id
");
$res1->execute();
while ($row = $res1->fetch(PDO::FETCH_ASSOC))
{
    echo "user $row[user_id] has runned $row[distance] km.\n";
}

This will return the sum of distances by user, not for all users at once.