Geek Answers Handbook

MySQL complex query incorrect results

I am trying to build a complex MySQL query, but it returns incorrect results ...

SELECT b.name AS batch_name, b.id AS batch_id, COUNT(DISTINCT s.id) AS total_students, COALESCE( SUM(s.open_bal), 0 ) AS open_balance, SUM( COALESCE(i.reg_fee, 0) + COALESCE(i.tut_fee, 0) + COALESCE(i.other_fee, 0) ) AS gross_fee, SUM( COALESCE(i.discount, 0) ) AS discount, COALESCE( SUM(s.open_bal), 0 ) + SUM( COALESCE(i.reg_fee, 0) + COALESCE(i.tut_fee, 0) + COALESCE(i.other_fee, 0) ) - SUM( COALESCE(i.discount, 0) ) AS net_payable, SUM( COALESCE(r.reg_fee, 0) + COALESCE(r.tut_fee, 0) + COALESCE(r.other_fee, 0) ) AS net_recieved, ( COALESCE( SUM(s.open_bal), 0 ) + SUM( COALESCE(i.reg_fee, 0) + COALESCE(i.tut_fee, 0) + COALESCE(i.other_fee, 0) ) - SUM( COALESCE(i.discount, 0) ) ) - ( SUM( COALESCE(r.reg_fee, 0) + COALESCE(r.tut_fee, 0) + COALESCE(r.other_fee, 0) ) ) AS balance_due FROM batches b LEFT JOIN students s ON s.batch = b.id LEFT JOIN invoices i ON i.student_id = s.id LEFT JOIN recipts r ON r.student_id = s.id WHERE s.inactive = 0 GROUP BY b.name, b.id;

It returns the following results ...

 | batch_name | total_students | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance | +------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+ | MS | 6 | 10000 | 0 | 0 | 10000 | 101000 | -91000 | +------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+

batch table

 | id | name | +-----+------+ | 9 | Ms | +-----+------+

Student's table

 | id | open_bal | batch | inactive | +-----+----------+-------+----------+ | 44 | -16000 | 9 | 0 | +-----+----------+-------+----------+ | 182 | 9000 | 9 | 0 | +-----+----------+-------+----------+ | 184 | -36000 | 9 | 0 | +-----+----------+-------+----------+ | 185 | 19000 | 9 | 0 | +-----+----------+-------+----------+ | 186 | 9000 | 9 | 0 | +-----+----------+-------+----------+ | 187 | 4000 | 9 | 0 | +-----+----------+-------+----------+

Billing Chart

 | id | student_id | reg_fee | tut_fee | other_fee | net_payable | discount | +------+------------+---------+---------+-----------+-------------+----------+ | | | | | | | | +------+------------+---------+---------+-----------+-------------+----------+

For the above student, accounts are not available.

Recipe Table

 | id | student_id | reg_fee | tut_fee | other_fee | status | +------+------------+---------+---------+-----------+------------+ | 8 | 44 | 0 | 0 | 1500 | confirmed | +------+------------+---------+---------+-----------+------------+ | 277 | 44 | 0 | 50000 | 0 | confirmed | +------+------------+---------+---------+-----------+------------+ | 26 | 182 | 0 | 0 | 1500 | confirmed | +------+------------+---------+---------+-----------+------------+ | 424 | 182 | 0 | 15000 | 0 | confirmed | +------+------------+---------+---------+-----------+------------+ | 468 | 182 | 0 | 15000 | 0 | confirmed | +------+------------+---------+---------+-----------+------------+ | 36 | 185 | 0 | 0 | 1500 | confirmed | +------+------------+---------+---------+-----------+------------+ | 697 | 185 | 0 | 15000 | 0 | confirmed | +------+------------+---------+---------+-----------+------------+ | 66 | 187 | 0 | 0 | 1500 | confirmed | +------+------------+---------+---------+-----------+------------+

Expected results using the above SQL query and tables ...

 | batch_name | total_students | open_bal | gross_fee | discount | net_payable | net_recieved | due_balance | +------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+ | MS | 6 | -11000 | 0 | 0 | 10000 | 101000 | -112000 | +------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+
+4
source share
2 answers

I have a solution, I have combined a lot of queries and that doubles according to some results. thanks.

0
source

You still did not provide complete information - there is no batch table, not even a table of existing recipes. In any case, I assume that we do not care that in the batch table, let's say, it's just a name and an identifier. The table of your receipts has several rows for the same student. This should result in multiple rows being returned for other tables due to all JOINs. Therefore, you SUM () several times the values ​​that should only be summed once, i.e. open_balance. This may be the key to the question of where the problem is, I would say that you need to move the information you need from the receipts table to the subqueries, but I'm not sure that you showed us your entire database. Try to remove the receipts table from the query and check the results again. If so, you should see what to do next, or at least provide us with more information.

EDIT: The request should be:

 SELECT b.name AS batch_name, b.id AS batch_id, COUNT(DISTINCT s.id) AS total_students, COALESCE( SUM(s.open_bal), 0 ) AS open_balance, SUM( COALESCE(i.reg_fee, 0) + COALESCE(i.tut_fee, 0) + COALESCE(i.other_fee, 0) ) AS gross_fee, SUM( COALESCE(i.discount, 0) ) AS discount, COALESCE( SUM(s.open_bal), 0 ) + SUM( COALESCE(i.reg_fee, 0) + COALESCE(i.tut_fee, 0) + COALESCE(i.other_fee, 0) ) - SUM( COALESCE(i.discount, 0) ) AS net_payable, SUM((SELECT SUM(COALESCE(receipts.reg_fee, 0) + COALESCE(receipts.tut_fee, 0) + COALESCE(receipts.other_fee, 0)) FROM receipts WHERE receipts.student_id = s.id)) AS net_recieved, ( COALESCE( SUM(s.open_bal), 0 ) + SUM( COALESCE(i.reg_fee, 0) + COALESCE(i.tut_fee, 0) + COALESCE(i.other_fee, 0) ) - SUM( COALESCE(i.discount, 0) ) ) - SUM((SELECT SUM(COALESCE(receipts.reg_fee, 0) + COALESCE(receipts.tut_fee, 0) + COALESCE(receipts.other_fee, 0)) FROM receipts WHERE receipts.student_id = s.id)) AS balance_due FROM batches b LEFT JOIN students s ON s.batch = b.id LEFT JOIN invoices i ON i.student_id = s.id WHERE s.inactive = 0 GROUP BY b.name, b.id;

This summarizes student data in the receipt table, even if it is on more than one row, returning only one row. Removing a join in the receipts table removes duplicate rows from other tables, so the calculations should now be correct.

One more thing: you have s.inactive = 0 in the WHERE clause, make sure that this does not apply to these calculations.

PS Why do not you know what a sub-request is, and ultimately you write such things?

+1
source

More articles:


All Articles