query shows up critical mistake.

hello , the last few days i've been trying to do a query, that sums values from two tables and then after it divides them by one column of one table. what's happening it's giving a wrong result and i have no idea why


                'costKM' => Repairs::query()
                    ->join('form', 'form.carplate', '=', 'form.carplate')
                    ->selectRaw("(convert(form.totalFuel, decimal(5,2))+convert(form.ports, decimal(5,2))+convert(repairs.cost, decimal(5,2)))/convert(form.startKM-form.currentKM, decimal(5,2)) AS costKM")
                    ->orderBy('form.carplate')
                    ->groupBy('form.carplate')

                    ->get(),
            ]);

so what am i getting? https://prnt.sc/SXe7XeuF3P3n on custoKM should appear 7.6 and it's appearing 7.0 the calculations above on the query should produce the result 7.6 why am i getting this? the calculations is (90+80+20)/25

Nakov

3 years ago

Best Answer

Level 73

So is it the query itself that returns the rounded number, or when you are displaying the number in the view you are maybe using round() function?

You can check with

dd(Repairs::query()
                    ->join('form', 'form.carplate', '=', 'form.carplate')
                    ->selectRaw("(convert(form.totalFuel, decimal(5,2))+convert(form.ports, decimal(5,2))+convert(repairs.cost, decimal(5,2)))/convert(form.startKM-form.currentKM, decimal(5,2)) AS costKM")
                    ->orderBy('form.carplate')
                    ->groupBy('form.carplate')

                    ->get());

what the result is. That will return a collection first of all.

peterpan26

3 years ago

Level 1

#attributes: array:1 [▼ "costKM" => "7.000000" ] #original: array:1 [▼ "costKM" => "7.000000" ] it shows in dd 7.00000 instead of the 7.6. the values i have to make the calculations shouldnt give this, how to fix this? i'm not using round function, at least that i know of

peterpan26

3 years ago

Level 1

i've converted all related columns that make the calculation to decimal and still appears 7.000000.

Please or to participate in this conversation.