sum a column inside a get?

@tykus i already know how to do it on select, but when connecting 3 tables in laravel i get errors, dont know if you already tried it but here's an example of what i want to achieve...

$users = User::join('posts', 'posts.user_id', '=', 'users.id')
              ->join('comments', 'comments.post_id', '=', 'posts.id')
              ->get(['users.*', 'SUM(posts.descrption)']);

@peterpan26 still, you need a raw expression. I don't assume to know why you're summing a description column 🤷‍♂️; or why you join the comments table when you don't use anything from that table:

$users = User::selectRaw('users.*, SUM(posts.descrption)')
    ->join('posts', 'posts.user_id', '=', 'users.id')
    ->join('comments', 'comments.post_id', '=', 'posts.id')
    ->get();

@tykus have you achieved accurate results with that query? just asking because i've just tried and the sums are wrong.

@peterpan26 this is your data; not mine. The suggested query is based on your original.

@tykus well, if you can help in this query would be highly appreciated.

$summaryData = Formulario::selectRaw('formulario.matricula,SUM(formulario.kmfim-formulario.quilometragem) AS kmsTotais')
        ->join('reparacoes', 'formulario.matricula', '=', 'reparacoes.matricula')
        ->join('viaverde','viaverde.matricula','=','reparacoes.matricula')
        ->groupBy(DB::raw("matricula"))
        ->get();

@peterpan26 what type is this formulario.kmfim-formulario.quilometragem?

@tykus both int fields

@peterpan26 the joins do what exactly; which table's matricula are you grouping on?

@peterpan26 sure, but in the context of this query; what is the purpose of joining? Shouldn't the following work?

$summaryData = Formulario::query()
  ->selectRaw('matricula, SUM(kmfim -quilometragem) AS kmsTotais')
  ->groupBy('matricula')
  ->get();

@tykus joining is to connect the 3 different modules that support data storage , and produce a report with those modules in a separate page.

yes just for this sum would work, but i believe that if i get the data correct for the sum i can start to add the other things i want to, like averages etc

@peterpan26 ok; so does this query work or not? Let's not start joining until the other tables are needed.

@tykus said matricula is ambiguous on groupby and i tried putting formulario.matricula but: SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.formulario.id' isn't in GROUP By, with the joins too

yes it works sorry my mistake i tried your query with one table and it works:

$summaryData = Formulario::query()
        ->selectRaw('matricula, SUM(kmfim -quilometragem) AS kmsTotais')
        ->groupBy('matricula')
        ->get();

now the trick would be to join for example reparacoes and get sum of reparacoes.valor, and also join viaverde and thet the sum of viaverde.custo, without affecting kmsTotais

@peterpan26 okay, so problem solved, or does the query need to be expanded to include more columns/constraints?

@tykus i've edited my last answere with : "now the trick would be to join for example reparacoes and get sum of reparacoes.valor, and also join viaverde and thet the sum of viaverde.custo, without affecting kmsTotais" something like:

->selectRaw('matricula, SUM(kmfim -quilometragem) AS kmsTotais,sum(viaverde.custo) as ViaverdeCost, sum(reparacoes.valor) as REPcost')

when i try to do it with the joins i get not accurate results

@tykus based on matricula. Can be any of them i believe scince theyre equal in all tables , could be formulario.matricula

@peterpan26 you'l most likely want to solve this problem with sub-queries; like this:

SELECT f.matricula, kmsTotais, sum_valor, sum_custo
FROM (
    SELECT matricula, SUM(kmfim - quilometragem) AS kmsTotais
    FROM formulario
    GROUP BY formulario.matricula
)  f
JOIN (
    SELECT reparacoes.matricula, SUM(reparacoes.valor) as sum_valor
    FROM reparacoes
    GROUP BY reparacoes.matricula
) r ON r.matricula = f.matricula
JOIN (
    SELECT viaverde.matricula, SUM(viaverde.custo) as sum_custo
    FROM viaverde
    GROUP BY viaverde.matricula
) v ON v.matricula = f.matriculas

Can you check this works for you in SQL before converting to Query Builder?

@tykus says column formulario.matricula unknown in field list i've tried on php my admin on sql section on overall database and on formulario table

@peterpan26 fixed above

@tykus says f.matriculas unknown in on clause

@peterpan26 typo, check the column name

@peterpan26 right? Are the numbers correct?

@tykus viaverde.custo is not

@peterpan26 typo again:

JOIN (
    SELECT viaverde.matricula, SUM(viaverde.custo) as sum_custo
    FROM viaverde
    GROUP BY viaverde.matricula
) v ON v.matricula = f.matriculas

@tykus it worked perfectly. i now just need it in query builder

@peterpan26 you want me to do that for you as well; or can you make an attempt yourself???

@tykus i dont know how to do it but i can try. Ill tell something if i get stuck or if i find answere thank you for everything

@peterpan26 you're not going to learn unless you make some attempt to problem solve yourself.

Or...

DB::query()
  ->selectRaw('f.matricula, kmsTotais, sum_valor, sum_custo')
	->from(
		DB::table('formularios')
			->selectRaw('formularios.matricula, SUM(kmfim - quilometragem) AS kmsTotais')
			->groupBy('formularios.matricula'),
		'f'
	)->joinSub(
		DB::table('reparacoes')
			->selectRaw('reparacoes.matricula, SUM(reparacoes.valor) as sum_valor')
			->groupBy('reparacoes.matricula'),
		'r.matricula', '=', 'f.matricula',
		'r'
	)->joinSub(
		DB::table('viaverde')
			->selectRaw('viaverde.matricula, SUM(viaverde.custo) as sum_custo')
			->groupBy('viaverde.matricula'),
		'v.matricula', '=', 'f.matricula',
		'v'
	)->get()