CODEWITHSUNDEEP
phpmysqllaravel
Vinny
Vinny

Reputation: 779

How to get sum of two different columns with Laravel Query Builder?

I'm trying to get the sum of two different columns using Laravel query builder, the plain SQL Query below works just fine, but I can't get it to work with the Laravel Query.

SELECT SUM(logins_sun + logins_mon) FROM users_stats WHERE id = 7; // returns: 1034

Here's what I have tried.

$stats = DB::table('users_stats')->where('id', '=', '7')->sum('logins_sun', '+', 'logins_mon'); // returns: 587.0

And here is my DB structure.

+----+------------+------------+
| id | logins_sun | logins_mon |
+----+------------+------------+
|  7 |     587    |     447    |
+----+------------+------------+

It was supposed to return 1034 but the Laravel Query is returning only the last value 587.0 .

How can I get it working?

Upvotes: 13

Views: 54825

Answers (4)

Anwar Khan
Anwar Khan

Reputation: 409

You can try with the sum() method like:

DB::table('users_stats')
    ->where('id', '7')
    ->sum(\DB::raw('logins_sun + logins_mon'));

Upvotes: 25

Nazmul Hasan
Nazmul Hasan

Reputation: 1977

You can run direct raw sql in laravel with the following way :

$sql = "SELECT SUM(logins_sun + logins_mon) FROM users_stats WHERE id = :ID";

$result = DB::select($sql,['ID'=>7]);

Upvotes: 1

Don't Panic
Don't Panic

Reputation: 41810

sum is an aggregate function and only takes one argument. It will sum the values of each row in a column. In your case, the query only returns one row, so the sum is just the value of that one column (the first argument passed to sum()). There may be some better way to do it, but I think you should be able to use a raw expression to return the sum of the two columns.

$stats = DB::table('users_stats')
             ->select(DB::raw('logins_sun + logins_mon'))
             ->where('id', '=', '7');

Upvotes: 12

user320487
user320487

Reputation:

Try passing a callback to the sum() and do the addition there like:

$stats = DB::table('users_stats')->where('id', '=', '7')->sum(function ($row) {
    return $row->logins_sun + $row->logins_mon;
});

Upvotes: 2

Related Questions