MySQL SUM returns unexpected value

Question

I've been trying to use SUM() to total up some numeric values in my database, but it seems to be returning unexpected values. Here is the PHP-side for generating the SQL:

public function calcField(string $field, array $weeks)
{
    $stmt = $this->conn->prepare('SELECT SUM(`'. $field .'`) AS r FROM `ws` WHERE `week` IN(?);');
    $stmt->execute([implode(',', $weeks)]);

    return $stmt->fetch(\PDO::FETCH_ASSOC)['r'];
}

Let's give it some example data for you folks at home:

$field = 'revenue';
$weeks = [14,15,16,17,18,19,20,21,22,23,24,25,26];

This returns this value:

4707.92

Without seeing the data, this may have seemed to have worked, but here's the rows for those weeks:

+----+------+------+---------+-------+---------+---------+------+----------+
| id | week | year | revenue | sales | gpm_ave | uploads | pool | sold_ave |
+----+------+------+---------+-------+---------+---------+------+----------+
|  2 |   14 | 2019 | 4707.92 |   292 |      13 |       0 | 1479 |       20 |
|  3 |   15 | 2019 | 4373.32 |   304 |      13 |       0 | 1578 |       19 |
|  4 |   16 | 2019 | 4513.10 |   275 |      14 |       0 | 1460 |       19 |
|  5 |   17 | 2019 | 4944.80 |   336 |      14 |       0 | 1642 |       20 |
|  6 |   18 | 2019 | 4343.87 |   339 |      13 |       0 | 1652 |       21 |
|  7 |   19 | 2019 | 3918.59 |   356 |      14 |       0 | 1419 |       25 |
|  8 |   20 | 2019 | 4091.20 |   247 |      19 |       0 | 1602 |       15 |
|  9 |   21 | 2019 | 4177.22 |   242 |      12 |       0 | 1588 |       15 |
| 10 |   22 | 2019 | 3447.88 |   227 |      18 |       0 | 1585 |       14 |
| 11 |   23 | 2019 | 3334.18 |   216 |      15 |       0 | 1675 |       13 |
| 12 |   24 | 2019 | 4736.15 |   281 |      13 |       0 | 1388 |       20 |
| 13 |   25 | 2019 | 4863.84 |   252 |      12 |       0 | 1465 |       17 |
| 14 |   26 | 2019 | 4465.95 |   281 |      21 |       0 | 1704 |       16 |
+----+------+------+---------+-------+---------+---------+------+----------+

As you can see, the total should be far greater than 4707.92 - and I notice that the first row revenue = 4707.92.

Here's what things get weird, if I add this into the function:

echo 'SELECT SUM(`'. $field .'`) AS r FROM `ws` WHERE `week` IN('. implode(',', $weeks) .');';

Which outputs:

SELECT SUM(revenue) AS r FROM ws WHERE week IN(14,15,16,17,18,19,20,21,22,23,24,25,26);

Copying and pasting this into MySQL CLI returns:

MariaDB [nmn]> SELECT SUM(revenue) AS r FROM ws WHERE week IN(14,15,16,17,18,19,20,21,22,23,24,25,26);
+----------+
| r        |
+----------+
| 55918.02 |
+----------+
1 row in set (0.00 sec)

Which, looks a lot more accurate. However, that very same SQL statement returns the first row value rather than summing the column for those weeks.

This function gets triggered by an AJAX script:

$d = new Page\Snapshot\D();

# at the minute only outputting dump of values to see what happens
echo '<pre>'. print_r(
        $d->getQuarterlySnapshot(new Page\Snapshot\S(), new App\Core\Date(), $_POST['quarter'], '2019'),
        1
    ). '</pre>';

The function $d->getQuarterlySnapshot function:

public function getQuarterlySnapshot(S $s, Date $date, int $q, string $year)
{
    switch($q)
    {
        case 1:
            $start = $year. '-01-01 00:00:00';
            $end = $year. '-03-31 23:59:59';
            break;
        case 2:
            $start = $year. '-04-01 00:00:00';
            $end = $year. '-06-30 23:59:59';
            break;
        case 3:
            $start = $year. '-07-01 00:00:00';
            $end = $year. '-09-30 23:59:59';
            break;
        case 4:
            $start = $year. '-10-01 00:00:00';
            $end = $year. '-12-31 23:59:59';
            break;
    }

    $weeks = $date->getWeeksInRange('2019', 'W', $start, $end);
    foreach ($weeks as $key => $week){$weeks[$key] = $week[0];}

    return [
        'rev' => $s->calcField('revenue', $weeks),
        'sales' => $s->calcField('sales', $weeks),
        'gpm_ave' => $s->calcField('gpm_ave', $weeks),
        'ul' => $s->calcField('uploads', $weeks),
        'pool' => $s->calcField('pool', $weeks),
        'sold_ave' => $s->calcField('sold_ave', $weeks)
    ];
}

So I don't overwrite the value anywhere (that I can see at least). How do I use SUM() with the IN() conditional?

Answer 1

As pointed out in the comments by @MadhurBhaiya:

Comma separated week_id values is passing as a single string in the query: week_id in ('1,2,3...,15') . MySQL implicitly typecasts this to 1 and thus gets the first row only. You will need to change the query preparation code

Led me to breaking up the single ? into named parameters using a foreach loop:

public function calcField(string $field, array $weeks)
{
    $data = [];
    $endKey = end(array_keys($weeks));

    $sql = 'SELECT SUM(`'. $field .'`) AS r FROM `ws` WHERE `week` IN(';

    foreach ($weeks as $key => $week)
    {
        $sql .= ':field'. $key;
        $sql .= ($key !== $endKey ? ',' : '');

        $data[':field'. $key] = $week;
    }

    $sql .= ');';

    $stmt = $this->conn->prepare($sql);
    $stmt->execute($data);

    return $stmt->fetch(\PDO::FETCH_ASSOC)['r'];
}

Now each numeric value is treated individually, which, is getting me the expected value.

Answer 2

Yes, it is showing only first row data in your query because you have mentioned where week IN (1,2,3,4,5,6,7,8,9,10,11,12,13); and your data shows that you have week 13 and 1 to 12 does not exist in your data from IN clause of your query.

Answer 3

You are selecting only the row with week 13, the week 1-12 is not existing in your data. That's why the result of your query is 43.900001525878906

Solution 1:

You might want to change the values in your IN()because you are filtering your data by the column week.

SELECT SUM(`revenue`) as `r` FROM `ws` WHERE `week` IN (13,14,15,16,17,18,19,20,21,22,23,24,25);

Solution 2:

You can change the week in your where clause to id

SELECT SUM(`revenue`) as `r` FROM `ws` WHERE `id` IN (1,2,3,4,5,6,7,8,9,10,11,12,13);