CODEWITHSUNDEEP
phpmysqllaravel
TechTimeGames
TechTimeGames

Reputation: 49

Laravel 5.7 - multiply two columns and add to final sum

I have two tables - a students table and a products table.

When i make a list of the students in a table, i need to see the total amount (sum) of payments that has been made, unfortunately it seems like the result is the correct sum but multiplied by the amount of rows.

Students table:

+----+----------+
| id |   name   |
+----+----------+
|  1 | Jonathan |
|  2 | Bob      |
+----+----------+

Products table:

+----+------------+-------+----------+
| id | student_id | money | quantity |
+----+------------+-------+----------+
|  1 |          1 |  1000 |        2 |
|  2 |          1 |  2000 |        1 |
|  3 |          2 |   500 |        5 |
|  4 |          2 |  3000 |        1 |
+----+------------+-------+----------+

Payments table:

+----+-------+------------+
| id | money | student_id |
+----+-------+------------+
|  1 |  5000 |          1 |
|  2 |  2000 |          1 |
|  3 |  2500 |          2 |
|  4 |  2500 |          2 |
+----+-------+------------+

In theory, the output of my query should be:

+-------------+----------+----------------+----------------+
| id          |   name   | payments_total | products_total |
+-------------+----------+----------------+----------------+
|           1 | Jonathan |  4000          |           7000 |
|           2 | Bob      |  5500          |          10000 |
+-------------+----------+----------------+----------------+

What i have tried:

        $teamStudents = DB::table('students')->where('students.team', $team)->orderBy('first_name', 'ASC') 
                                ->join('products', 'students.id', '=', 'products.student_id')
                                ->join('payments', 'students.id', '=', 'payments.student_id')
                                ->select('students.first_name AS first_name', 'students.last_name AS last_name', 'students.created_at AS created_at', DB::raw('SUM(products.money * products.amount) AS products_total'), DB::raw('SUM(payments.money) AS payments_total'), 'students.id AS id')
                                ->groupBy('students.id')
                                ->get();

It returns no error except for the fact that the returned "payments_total" is inaccurate and multiplied by the amount of rows for some reason.

So my question is: How do i get around this and what have i done wrong? I've been googling for an hour with no result.

Is my query an issue or the way i've set it up, if so, what would the correct solution be?

Upvotes: 0

Views: 2483

Answers (2)

nakov
nakov

Reputation: 14268

With your edit I was able to solve the problem that you have, but in your edit you use couple of things for which I don't have data, such as the $team, first_name and last_name of the students. But anyway, here is a solution for your problem, you have to use subqueries in order to solve this:

$teamStudents = DB::table('students')->orderBy('name', 'ASC')
    ->leftJoin(DB::raw('(select SUM(products.money * products.quantity) AS products_total, student_id from products group by student_id) products'), 'students.id', '=', 'products.student_id')
    ->leftJoin(DB::raw('(select sum(payments.money) as payments_total, student_id from payments group by student_id) payments'), 'students.id', '=',
        'payments.student_id')
    ->select('students.name', 'payments.payments_total', 'products.products_total', 'students.id AS id')
    ->groupBy('students.id')
    ->get();

I am not sure if technically I will be correct, but the problem is because you use multiple joins, so that's why the results are doubled, if you don't use subqueries.

Upvotes: 2

Jane
Jane

Reputation: 438

There's no need to join in this case, you don't use it anyways.

$teamStudents = DB::table('students')
    ->select('id, name')
    ->selectRaw('select sum(money) from payments where student_id = students.id as payments_total')
    ->selectRaw('select sum(money) from products where student_id = students.id as products_total')
    ->orderBy('name')
    ->get();

Upvotes: 0

Related Questions