Laravel Query Builder - sum() method issue

Question

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)

Answer 1

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

Answer 2

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