mysql small issue

I am trying following query...

SELECT b.name AS batch_name, b.id AS batch_id,
COUNT( 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  
FROM batches b
LEFT JOIN students s on s.batch = b.id
LEFT JOIN invoices i on i.student_id = s.id
GROUP BY b.name, b.id;

result set

| batch_name | batch_id  | total_students | open_balance | gross_fee |
+------------+-----------+----------------+------开发者_JAVA百科--------+-----------+
|  ba        | 11        | 44             | 0            | 1782750   |
+------------+-----------+----------------+--------------+-----------+

But its giving unexpted results, and if i remove sum( COALESCE(i.reg_fee,0) + COALESCE(i.tut_fee,0) + COALESCE(i.other_fee,0) ) AS gross_fee and LEFT JOIN fm_invoices i on i.student_id = s.id, it gives expected/correct results as following...

| batch_name | batch_id  | total_students | open_balance | gross_fee |
+------------+-----------+----------------+--------------+-----------+
|  ba        | 11        | 34             | 0            | 0         |
+------------+-----------+----------------+--------------+-----------+

I am sure, i am doing something and i am trying every option since last hour, please help.

---

I assume your question is something like:

Why does COUNT(s.id) return 44 in the first query and 34 in the second query, and how can I make it count 34 students while I sum the invoices in the same query?

You have multiple invoices for some of your students, and the join results in multiple rows with the same s.id. When you count them, it counts each of these multiple rows.

You should use COUNT(DISTINCT s.id) to make the query count each student id only once, even when it appears multiple times as a consequence of the join to invoices.

---

Re your question about what to change, just change COUNT(s.id) to COUNT(DISTINCT s.id). The rest of the query looks fine, if I have a correct understanding of what you want it to do.