Laravel Query Builder - sum() method issue

php mysql database laravel query-builder

123,666

Solution 1

You don't need to use select() or get() when using the aggregate method as sum:

$purchases = DB::table('transactions')
    ->join('categories', 'transactions.category_id', '=', 'categories.id')
    ->where('categories.kind', '=', 1)
    ->sum('transactions.amount');

Solution 2

If one needs to select SUM of a column along with a normal selection of other columns, you can sum select that column using DB::raw method:

DB::table('table_name')
    ->select('column_str_1', 'column_str_2', DB::raw('SUM(column_int_1) AS sum_of_1'))
    ->get();

Solution 3

You can get some of any column in Laravel query builder/Eloquent as below.

$data=Model::where('user_id','=',$id)->sum('movement');
return $data;

You may add any condition to your record. Thanks

Solution 4

Aggregates methods of query builder are:

  1) max() 
  2) min()  
  3) sum()  
  4) avg()   
  5) count()

You can use like,

 $purchases = DB::table('transactions')
     ->join('categories', 'transactions.category_id', '=', 'categories.id')
     ->where('categories.kind', '=', 1)
     ->sum('transactions.amount');

View more solutions

123,666

Author by

burn15

Updated on February 03, 2022

Comments

burn15 over 2 years
I'm new to laravel and I have some issues with the query builder. The query I would like to build is this one:
```
SELECT SUM(transactions.amount)
FROM transactions
JOIN categories
ON transactions.category_id == categories.id 
WHERE categories.kind == "1"
```
I tried building this but it isn't working and I can't figure out where I am wrong.
```
$purchases = DB::table('transactions')->sum('transactions.amount')
    ->join('categories', 'transactions.category_id', '=', 'categories.id')
    ->where('categories.kind', '=', 1)
    ->select('transactions.amount')
    ->get();
```
I would like to get all the transactions that have the attribute "kind" equal to 1 and save it in a variable. Here's the db structure:

transactions(id, name, amount, category_id)

categories(id, name, kind)
burn15 almost 9 years

Thank you so much limonte! I read a lot but didn't get it clear!
Limon Monte almost 9 years

@burn15 you're welcome :) don't forget to accept the answer for further readers.
burn15 almost 9 years

I am so sorry! I didn't knew I had to do that, I'm a newbie in stackoverflow! I am having other issues in my code, and maybe you can help me out. Sorry but I don't really get laravel query building methods. I have this query, which takes all the transactions that have categories with the kind equal to 1 and then I print the results to show them to the user. The problem is that all users can see them! The relations between the main tables are good, I guess something is wrong with the query, probably I should get the transactions created by the connected user. What should I change?
burn15 almost 9 years

Here's the query: '$purchases = DB::table('transactions')->select('transactions.id', 'transactions.name', 'amount', 'description', 'date', 'category_id') ->join('categories', 'transactions.category_id', '=', 'categories.id') ->where('categories.kind', '=', 1) ->get();'
UrbanwarfareStudios about 2 years

It is worth noting that sum() needs to be at the end of the command - otherwise it will error with the your next function being called on a string (for example) sum()->groupBy('price') would throw (Laravel 8): Call to a member function groupBy() on string