CODEWITHSUNDEEP
mysqlgroup-byaggregate
seoppc
seoppc

Reputation: 2824

mysql inner join with group by

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

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

And its outputting the following results...

| amount | discount | netpay   | batch | 
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2011  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2010  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2009  |
+--------+----------+----------+-------+
| 2500   | 500      | 2000     | 2008  |
+--------+----------+----------+-------+

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.

Upvotes: 1

Views: 3759

Answers (1)

Galz
Galz

Reputation: 6832

So you need something like:

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 batches b 
LEFT JOIN subscribers s on s.bacth_id = b.id
LEFT JOIN invoices i on i.subs_id = s.id
GROUP BY b.name

(guessing the relationship between subscribers and batches).

Upvotes: 2

Related Questions