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?

You probably mean to use where id IN (... instead of where week in (.. because the data that you are showing at the end is from id = 1 to 13 — Madhur Bhaiya
– Madhur Bhaiya, Commented Sep 4, 2019 at 10:33
@MadhurBhaiya ah, no I do mean week - but didn't actually check the week values xD one sec, lemme quickly test with another.. more populated range and get back to you xD — treyBake
– treyBake, Commented Sep 4, 2019 at 10:35
Not a good idea storing money values as floating point numbers — Ed Heal
– Ed Heal, Commented Sep 4, 2019 at 10:36
SHOW CREATE TABLE ws like @EdHeal suggestion it seams you are using float where you should have used decimal .. floats are approximate values so indeed like Ed said not smart to use approximate values to indicate money values. — Raymond Nijland
– Raymond Nijland, Commented Sep 4, 2019 at 10:41
alter the table to add a new column with decimal type revenue_decimal .. Run UPDATE SET revenue_decimal = revenue .... and check the data between... when the data is correct drop the float type and rename the decimal type.. Or create a new table which is also possible and run a INSERT INTO new_table SELECT * FROM old_table.. Anny how don't do a datatype conversion on life data most likely in this case nothing bad will happen but better to be safe then sorry.. — Raymond Nijland
– Raymond Nijland, Commented Sep 4, 2019 at 10:46

treyBake · Accepted Answer · 2019-09-05 08:27:37Z

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.

Ishpreet · Accepted Answer · 2019-09-04 10:38:02Z

1

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.

answered Sep 4, 2019 at 10:38

Ishpreet

6795 silver badges24 bronze badges

Comments

oycarlito · Accepted Answer · 2019-09-04 10:39:29Z

0

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);

answered Sep 4, 2019 at 10:39

oycarlito

1221 silver badge14 bronze badges

1 Comment

treyBake Over a year ago

solution 2 != a solution, I wanted week ^

Collectives™ on Stack Overflow

MySQL SUM returns unexpected value

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related