title: mysql inner join with group by
body: <p>I am using the following query to get correct results for each batch. For example if I would to like to see the total of invoice for batch 2010...</p>
  
  <pre><code>SELECT COALESCE(sum(i.amount),0) AS amount, 
      COALESCE(sum(i.discount),0) AS discount,
      COALESCE(sum(i.amount) - sum(i.discount),0) AS netpay,
      b.name AS batch
  FROM fm_batches b
      INNER JOIN fm_invoices i
      LEFT JOIN fm_students s ON i.student_id = s.id
  GROUP BY b.name
  </code></pre>
  
  <p>And its outputting the following results...</p>
  
  <pre><code>| amount | discount | netpay   | batch | 
  +--------+----------+----------+-------+
  | 2500   | 500      | 2000     | 2011  |
  +--------+----------+----------+-------+
  | 2500   | 500      | 2000     | 2010  |
  +--------+----------+----------+-------+
  | 2500   | 500      | 2000     | 2009  |
  +--------+----------+----------+-------+
  | 2500   | 500      | 2000     | 2008  |
  +--------+----------+----------+-------+
  </code></pre>
  
  <p>I am sure that I am doing something wrong in my query as its giving the wrong results. It should return 0 if nothing found for batch 2010.  thanks.</p>