I'm having trouble with a query, have been stuck with it for a few days. It's not too complex but after trying and failing I've decided to post it here.
I have a table with 4 fields (id | period | type | amount). period has the last two digits from the year, and the two digits from month (values like 1512, 1601) and type is 1,2 or 3. What I'm trying to do is sum the amount field grouped by period and type, but the problem it's that I'm trying to do it with grouping type in one row. This will be easier to show with an example of data and desire output.
Data:
+-------+----------+-----------+--------+
| id | period | type | amount |
+-------+----------+-----------+--------+
| 1 | 1511 | 1 | 15 |
| 2 | 1511 | 1 | 20 |
| 3 | 1511 | 2 | 15 |
| 4 | 1511 | 3 | 30 |
| 5 | 1512 | 1 | 20 |
| 6 | 1512 | 1 | 20 |
| 7 | 1512 | 2 | 35 |
| 8 | 1512 | 2 | 40 |
| 9 | 1512 | 2 | 45 |
| 10 | 1512 | 2 | 45 |
| 11 | 1512 | 3 | 15 |
| 12 | 1601 | 1 | 20 |
| 13 | 1601 | 2 | 25 |
| 14 | 1601 | 2 | 40 |
| 15 | 1601 | 3 | 60 |
| 16 | 1601 | 3 | 65 |
+-------+----------+-----------+--------+
Desire output:
+---------+--------+--------+--------+
| period | type1 | type2 | type3 |
+---------+--------+--------+--------+
| 1511 | 35 | 15 | 30 |
| 1512 | 40 | 165 | 15 |
| 1601 | 20 | 65 | 125 |
+---------+--------+--------+--------+
And what I have so far is this query, which shows one row for type, and not what I want:
SELECT period, type, sum(amount) FROM payments GROUP BY period, type;
