Subtracting sums in one table from sums in another

I have two mysql tables: earning and redemption. Data in the earning table adds to a running balance and data in the redemption table subtracts from that balance. Each table has an hours column and an amount column.

(There are various reasons why 开发者_Go百科these aren't negative and positive values in the same table, but even with these reasons I recognize now that this is likely a poor schema design, but... for now I'm stuck with it).

How can I get a current balance for both the hours and amounts fields? More specifically, how can I write a single query that will give me SUM(earning.hours) - SUM(redemption.hours) and SUM(earning.amount) - SUM(redemption.amount), grouped by a Common_ID?

---

Some sample data. Other fields exist in each table, but I'm not concerned with them at the moment.

Earning:

+----+-----------+-------+--------+
| id | common_id | hours | amount |
+----+-----------+-------+--------+
| 1  | 234       | 1.03  | 15.75  |
| 2  | 234       | 2.06  | 33.00  |
| 3  | 237       | 2.11  | 12.29  |
| 4  | 237       | 3.50  | 18.63  |
+----+-----------+-------+--------+

Redemption:

+----+-----------+-------+--------+
| id | common_id | hours | amount |
+----+-----------+-------+--------+
| 1  | 234       | 2.50  | 30.00  |
| 2  | 234       | 0.50  | 5.68   |
| 3  | 237       | 1.00  | 8.00   |
+----+-----------+-------+--------+

Desired result:

+-----------+---------------+----------------+
| common_id | hours_balance | amount_balance |
+-----------+---------------+----------------+
| 234       | 0.09          | 13.07          |
| 237       | 4.61          | 22.92          |
+-----------+---------------+----------------+

---

You need to perform the grouping separately.

SELECT e.Common_ID,
  SUM(e.hours) - MIN(g.SumHours),
  SUM(e.amount) - MIN(g.SumAmount)
FROM earning e JOIN (
    SELECT Common_ID, SUM(hours) SumHours, SUM(amount) SumAmount
    FROM redemption
    GROUP BY Common_ID
  ) g ON e.Common_ID = g.Common_ID
GROUP BY Common_ID

Note: as Lamak pointed out, if you have an common_id that doesn't exists in both tables, you will need a LEFT JOIN and possibly another UNION with a RIGHT JOIN.