mysql wrong results on big query

Question

Please help me to fix mysql query and get correct results...

Please see dataset for tables as following...

students

| id | name  | batch | discount | open_bal | inactive |
+----+-------+-------+----------+----------+----------+
|  1 | Ash   | 19    | 0        | -5000    | 0        |
+----+-------+-------+----------+----------+----------+
|  2 | Tuh   | 15    | 0        | 0        | 0        |
+----+-------+-------+----------+----------+----------+

invoices

| id   | invoice_num | student_id | reg_fee | tut_fee | other_fee | discount |
+------+-------------+------------+---------+---------+-----------+----------+
|  1   | 2011/1      | 1          | 5000    | 0       | 0         |  0       |
+------+-------------+------------+---------+---------+-----------+----------+
|  137 | 2011/137    | 1          | 15000   | 0       | 0         |  0       |
+------+-------------+------------+---------+---------+-----------+----------+
|  169 | 2011/169    | 2          | 15000   | 0       | 0         |  0       |
+------+-------------+------------+---------+---------+-----------+----------+

recipts

| id   | recipt_num  | student_id | reg_fee | tut_fee | other_fee | status     |
+------+-------------+------------+---------+---------+-----------+------------+
|  264 | 2011/264    | 1          | 0       | 15000   | 0         |  confirmed |
+------+-------------+------------+---------+---------+-----------+------------+
|  18  | 2011/18     | 2          | 0       | 5250    | 0         |  confirmed |
+------+-------------+------------+---------+---------+-----------+------------+
|  251 | 2011/251    | 2          | 4650    | 0       | 0         |  pending   |
+------+-------------+------------+---------+---------+-----------+------------+

batches

| id  | name     |
+-----+----------+
|  19 | S.T-11   |
+-----+----------+
|  15 | Mc/11-13 |
+-----+----------+

I want to achieve report according to batches....

Batch id - batch id from batches table
Batch Name - batch name from batches table
Total Students - count(s.id) from students table group by batch
Opening Bal - sum(s.openbal) from students table
Gross Fee - sum(reg_fee+tut_fee+other_fee) from invoices table
Discount - sum(i.discount) from invoices table
Net Payable - (openbal + grossfee) - discount
Net Received - sum(reg_fee+tut_fee+other_fee) from recipts table where r.status = 'confirmed'
Due Balance - Net Payable - Net Received

expected report

| batch_id | batch_name | total_students | opening_bal | gross_fee | discount | net_payable | net_recieved | due_balance |
+----------+------------+----------------+-------------+-----------+----------+-------------+--------------+-------------+
|  15      | 2011/264   | 1              | 0           | 15000     | 0        |  15000      |    5250      | 9750        |
+----------+------------+----------------+-------------+-----------+----------+-------------+--------------+-------------+
|  19      | S.T-11     | 1              | -5000       | 20000     | 0        |  15000      | 15000        | 0           |
+----------+------------+----------------+-------------+-----------+----------+-------------+--------------+-------------+

I have tried using following query but its giving wrong results.

SELECT b.name AS batch_name, 
       b.id AS batch_id, 
       COUNT( s.id ) AS total_students, 
       COALESCE( s.open_bal, 0 ) AS open_balance, 
       COALESCE( sum( i.reg_fee + i.tut_fee + i.other_fee ) , 0 ) AS gross_fee,
       COALESCE( s.discount, 0 ) , 
       COALESCE( sum( i.reg_fee + i.tut_fee + i.other_fee ) , 0 ) - 
                 COALESCE( s.discount, 0 ) AS net_payable, 
       COALESCE( sum( r.reg_fee + r.tut_fee + r.other_fee ) , 0 ) AS net_recieved,
       COALESCE( s.discount, 0 ) , 
       COALESCE( sum( i.reg_fee + i.tut_fee + i.other_fee ) , 0 ) - 
                 COALESCE( s.discount, 0 ) - 
                 COALESCE( sum( r.reg_fee + r.tut_fee + r.other_fee ) , 0 ) 
                 AS due_balance
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 and r.status = 'confirmed'
GROUP BY b.name;

please help me to rewrite this query...

Answer 1

Talking about SQL this line is quite certainly wrong:

GROUP BY b.name;

The GROUP BY should contain every element of the select which is not an aggregate expression.

Try the query using:

GROUP BY b.name,b.id,COALESCE(s.open_bal,0), COALESCE(s.discount,0);

When you do not make the right GROUP BY expression MySQL makes his own improved and simplified group by, which avoids a query rejection but produce higly unexpectable results, especially if your query is complex.

If you do not need a distinct result row for each s.open_bal and s.discount, then maybe you do not need theses (duplicates) data in the select.

Then I did not took the time to analyze the complete query. But your needs seems quite complex. I would say Divide and conquer, KISS (Keep It Stupid Simple), make several queries you fully understand instead of one huge query. Especially if requirements from some of the results differs (some working on details, some working on aggregates, and some working on different aggregates, etc), as you would maybe need some window functions ("partition by" keyword) that you do not have on MySQL.

Answer 2

maybe you should try to fix your sum like this example:

COALESCE( sum( i.reg_fee + i.tut_fee + i.other_fee ) , 0 ) //bad
 sum( COALESCE(i.reg_fee,0) + COALESCE(i.tut_fee,0) + COALESCE(i.other_fee,0) )  //good