Mysql: Sum for 12 months of certain year, with left join on same month

Question

I have one table Income

+------------+--------+--------+
|    Date    | Amount | Status |
+------------+--------+--------+
| 2012-01-03 |    200 |      4 |
| 2012-02-12 |    300 |      4 |
| 2012-07-07 |    200 |      4 |
| 2012-08-15 |    300 |      4 |
| 2012-08-17 |    250 |      4 |
| 2012-09-17 |    250 |      3 |
+------------+--------+--------+

And other one Outcome

+------------+--------+
|    Date    | Amount |
+------------+--------+
| 2012-03-22 |    50  |
| 2012-07-02 |    50  |
| 2012-08-19 |    100 |
| 2012-08-22 |    70  |
+------------+--------+

And when I join those two tables (first one only where status is 4) I need to get result as this one:

+------+-----------+--------+---------+--------+
| Year |   Month   | Income | Outcome | Profit |
+------+-----------+--------+---------+--------+
| 2012 | January   |    200 |       0 |    200 |
| 2012 | February  |    300 |       0 |    300 |
| 2012 | March     |      0 |      50 |    -50 |
| 2012 | April     |      0 |       0 |      0 |
| 2012 | Jun       |      0 |       0 |      0 |
| 2012 | July      |    200 |      50 |    150 |
| 2012 | August    |    550 |     170 |    380 |
| 2012 | September |      0 |       0 |      0 |
| 2012 | October   |      0 |       0 |      0 |
| 2012 | November  |      0 |       0 |      0 |
| 2012 | December  |      0 |       0 |      0 |
+------+-----------+--------+---------+--------+

How to make query to get result as this one?

Thank you very much,

Answer 1

First you need to merge your 2 tables into one to be able to group it by month all together. I used UNION for that. And whole query ends up like this:

SELECT DATE_FORMAT(date, '%Y') AS year, DATE_FORMAT(date, '%M') AS month, SUM(income) AS income, SUM(outcome) AS outcome, (SUM(income)-SUM(outcome)) AS profit
FROM (
        SELECT date, amount AS income, 0 AS outcome FROM income_t
    UNION
        SELECT date, 0 AS income, amount AS outcome FROM outcome_t
    UNION
        SELECT '2012-01-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-02-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-03-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-04-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-05-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-06-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-07-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-08-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-09-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-10-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-11-01' AS date, 0 AS income, 0 AS outcome
    UNION
        SELECT '2012-12-01' AS date, 0 AS income, 0 AS outcome
) AS merged
GROUP BY DATE_FORMAT(date, '%Y%m')
ORDER BY date ASC;

I tested it and it's working fine.

---

Well, I did misunderstood your question about all months, but now it is as you want it.

Answer 2

First, you need to start with a driver table that has all 12 months. You are asking for rows in the output that are not in the input:

select driver.year, driver.monname, coalesce(i.amt, 0) as Income,
       coalesce(o.amt, 0) as Outcome,
       coalesce(i.amt, 0) - coalesce(o.amt, 0) as Profit
from (select 'January' as monname, 1 as mon, 2012 as yr union all
      select 'February', 2, 2012 union all
      . . .
      select 'December', 12, 2012
     ) driver left outer join
     (select year(date) as yr, month(date) as mon, sum(amount) as amt
      from income
      where status = 4
      group by year(date), month(date)
     ) i
     on driver.yr = i.yr and driver.mon = i.mon left outer join
     (select year(date) as yr, month(date) as mon, sum(amount) as amt
      from outcome
      group by year(date), month(date)
     ) o
     on driver.yr = o.yr and driver.mon = o.mon;

Your question is rather tricky for a beginner in several ways. First you need the driver table to get all the rows (and you need to fill in the missing rows in the table). Second, you need to be careful about how you mix the data from different tables. A poor choice of joins would result in a cartesian product.