mysql query help required [duplicate]

This question already has answers here: Closed 11 years ago.

Possible Duplicate:

complex my sql query wrong results

I am trying to build complex mysql query but its returning wrong 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;

Am i doing something wrong as sum(open_bal) results are wrong.

Sample Results...

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

A开发者_如何学Pythonbove results are wrong, please check following for table structure

Students table

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

Invoices Table

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

No invoices are available for above students id.

Recipts 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 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     |
+------------+-----------------+----------+-----------+----------+-------------+--------------+-------------+

Please check and reply, thanks.

---

You have to add student_id into the GROUP BY expression.

You're making several outer joins which means that all data will be used in the query, but also there are several rows for some students in the receipts table, so they're amounts will be duplicated (because there will be several rows with that s.id).

Take a look in the following join:

        r.id     s.id     open_bal
        --------------------------
        8       | 44    | -16000  
        277     | 44    | -16000  
        26      | 182   |   9000  
        424     | 182   |   9000  
        468     | 182   |   9000  
        36      | 185   |  19000
        697     | 185   |  19000
        66      | 187   |   4000
        null    | 184   | -36000
        null    | 186   |   9000

Sum it up and you'll get 10 000.