complex MySQL query wrong results

Question

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;

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

batches table

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

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

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

what u exact want?? and would u plz paste some data..so that we can experiment — xkeshav
– xkeshav, Commented Oct 12, 2011 at 7:44
No way to answer that without knowing anything about the structure of the tables or the data in them... — Nikoloff
– Nikoloff, Commented Oct 12, 2011 at 7:45
@Nikoloff i am going to add table structure, thanks for asking, — seoppc
– seoppc, Commented Oct 12, 2011 at 7:48

Nikoloff · Accepted Answer · 2011-10-13 07:55:30Z

1

You still haven't provided full information - no batches table, even the not existing recipts table.. Anyway, I assume we don't care whats in the batches table, lets say it's just the name and id. Your receipts table have multiple rows for the same student. This should result in multiple rows returned for the other tables as well, due to all the JOINs. Therefore you SUM() multiple times values which must be summed just once, i.e. open_balance. This could be a clue as to where the problem is, I'd say you have to move the info that you need from 'the receipts table into subqueries, but I'm not sure you've shown us the entirety of your DB. Try removing the receipts table from the query and check the results again. If that's it, you should see what to do from there on or at least give more info to us.

EDIT: The query 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 will sum students data in the receipts table even if it's on more than one row, returning just one row. Removing the join to the receipts table removes duplicate lines from the other tables, so the calculations should now be correct.

One more thing - you've got s.inactive = 0 in the WHERE clause, make sure it's not relevant to this calculations.

P.S. How come you don't know what a sub query is and you end up writing stuff like that?

edited Oct 13, 2011 at 7:55

answered Oct 12, 2011 at 16:18

Nikoloff

4,1602 gold badges19 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

seoppc Over a year ago

yes if i remove recipts from query, it shows correct open_balance, how should i fix this? i don't have idea about sub queries, please help, thanks.

seoppc Over a year ago

s.inactive = 0 is related to students table, its a Boolean to show if user if active or not.

Nikoloff Over a year ago

Of course it is, it's just another thing worth mentioning, cause it can mess with the calculations

seoppc Over a year ago

its returning correct value for open_bal but now its not giving correct value for net_received

Nikoloff Over a year ago

I edited the post with the entire query pasted there, not just the corrections. You should've at least mentioned what the problem is. You're not trying very hard to help fix you own problem.. I'll be offline for a while, hope that works. If not - check the results again and try to work on that, you now know what a sub query is.

|

seoppc · Accepted Answer · 2011-10-22 19:40:49Z

0

I have got the solution, i was joining lots of queries together and that's by some results are doubling. thanks.

answered Oct 22, 2011 at 19:40

seoppc

2,8227 gold badges46 silver badges79 bronze badges

Collectives™ on Stack Overflow

complex MySQL query wrong results

2 Answers 2

6 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related