How do I do this in MYSQL?

Question

I have following personal finance sample data in MySQL:

+---------------------------------------------------------------------------+
| id |    date    | amount | year | month |  description   | type | balance |
+---------------------------------------------------------------------------+
|  4 | 2015-02-03 | 563.00 | 2015 |   2   | Some text here |  out | -342.00 |
+---------------------------------------------------------------------------+
|  3 | 2015-02-01 | 102.95 | 2015 |   2   | Some text here |  in  |  221.00 |
+---------------------------------------------------------------------------+
|  2 | 2015-01-17 | 586.38 | 2015 |   1   | Some text here |  out |  184.96 |
+---------------------------------------------------------------------------+
|  1 | 2015-01-09 | 421.15 | 2015 |   1   | Some text here |  in  |  771.34 |
+---------------------------------------------------------------------------+

What I want is some kind of view/query. A single row for each month in selected year where SUM(amount) is seperate for each type, kinda like this:

+-------------------------------------+
| year | month | total_in | total_out | 
+-------------------------------------+
| 2015 |   2   |  xxx.xx  |  xxx.xx   |
+-------------------------------------+
| 2015 |   1   |  xxx.xx  |  xxx.xx   |
+-------------------------------------+

With following query

SELECT year, month, type, SUM(amount) as total_amount
FROM table
WHERE year = 2015
GROUP BY month, type
ORDER BY month
DESC

I only come as far as this:

+------------------------------------+
| year | month | type | total_amount |
+------------------------------------+
| 2015 |   2   |  in  |    xxx.xx    |
+------------------------------------+
| 2015 |   2   |  out |    xxx.xx    |
+------------------------------------+
| 2015 |   1   |  in  |    xxx.xx    |
+------------------------------------+
| 2015 |   1   |  out |    xxx.xx    |
+------------------------------------+

What is it that I'm missing here?

Thank you!

Answer 1

You are close. You need conditional aggregation:

SELECT year, month, 
       SUM(case when type = 'in' then amount else 0 end) as total_in,
       SUM(case when type = 'out' then amount else 0 end) as total_out
FROM table
WHERE year = 2015
GROUP BY year, month
ORDER BY year desc, month DESC;