Search code examples
phpmysqlpdosumsqldatatypes

Why SUM(`column`) returns a string instead of an integer?

I'm using Laravel and have a query that selects using DB::raw() SUM() of a column:

DB::raw("SUM(points) as game_points")

I have installed mysqldn and AFAIK Laravel uses PDO.

But game_points is a string, no matter what type of the column is. (It's an integer column)

Also if I do:

DB::raw("COUNT(id) as foo_bar")

foo_bar is returned as an integer.

Solution

  • It's neither Laravel or PDO issue.

    According to MySQL manual, SUM() returns a DECIMAL value for exact-value arguments (integer or DECIMAL). And the only way to represent DECIMAL type in PHP is string, for two reasons:

    • it can overflow the PHP's int type, being bigger than PHP_INT_MAX.
    • also, in case the returned value being a decimal number, it can lose precision due to inherently imprecise nature of floating point numbers (for example DECIMAL(10,2) can accurately store 0.1, while the closest thing php's native float-type can store is 0.1000000000000000055511151231257827021181583404541015625 )

    due to these precautions the value is returned as string, and you are supposed to convert it manually according to the expecting value - either using standard PHP type casting or using some dedicated math functions such as bcmath.

    But you want to get int of float right from database, you can cast the sum() result

    CAST(SUM(points) AS FLOAT) AS game_points -- for float values
or
CAST(SUM(points) AS UNSIGNED) AS game_points -- for integer values