how to make a join here without messing up the query sum?

hello i got the following code that produces a sum correctly done, but whenever i try to join the table to another to concat some results in the same dashboard it douobles up the values.


        $data = Reparacoes::select(DB::raw("SUM(valor) as sum, matricula"))
       
                               ->orderBy("matricula")

                               ->groupBy(DB::raw("matricula"))

                               ->get();
 return view('page',  ['data' => $data]);

if i add this to the code as follows it doubles up the values:


   $data = Reparacoes::select(DB::raw("SUM(valor) as sum, matricula"))
       ->join('formulario', 'reparacoes.matricula', '=', 'formulario.matricula')
                               ->orderBy("matricula")

                               ->groupBy(DB::raw("matricula"))

                               ->get();

how to i produce the same sum as in the first statement but with the join? so i can fetch more results and query as i wish

cwhite

3 years ago

Level 19

When joining, you often need to specify the table with the column:

$data = Reparacoes::select(DB::raw("SUM(reparacoes.valor) as sum, reparacoes.matricula"))
       ->join('formulario', 'reparacoes.matricula', '=', 'formulario.matricula')
       ->orderBy("reparacoes.matricula")
       ->groupBy(DB::raw("reparacoes.matricula"))
       ->get();

peterpan26

3 years ago

Level 1

cwhite this is the sum it's currently doing , now its not the double its more multiplier, i dont get this... i dont understand why its grabbing this values .

results: reparacoes table: Matricula: te-st-02 | Valor:11 Matricula: te-st-02 | Valor:9 Matricula: te-st-03 | Valor:15

AND ON THE queury result page matricula | totalReparacoes TE-ST-02 | 280 TE-ST-03 | 15

Please or to participate in this conversation.