27

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.

Ivanka Todorova
  • 9,964
  • 16
  • 66
  • 103
  • Check [this question](http://stackoverflow.com/questions/1197005/how-to-get-numeric-types-from-mysql-using-pdo). – Vatev Mar 17 '16 at 09:56

2 Answers2

42

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.

hanshenrik
  • 19,904
  • 4
  • 43
  • 89
Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • 5
    Interesting, so that's why `CAST(SUM(column) AS INTEGER)` still works and returns an integer. – Marcel Hernandez Jan 27 '20 at 23:20
  • 1
    as of at least mysql 8.0 AS INTEGER throws error. so u need to write AS UNSIGNED. for [more](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) – Tadas V. Nov 15 '22 at 10:35
0

You could try to convert that string to an integer by using CONVERT(INT, game_points)

According to bugs.mysql.com, sum() returns a NEWDECIMAL datatype, which is equivalent to string in php, while count() returns an integer datatype.

SEsterbauer
  • 79
  • 6