按种类选择总和和分组

Question

I'm trying to fill the data array for a Morris Chart with data from my MySql Database.

I have the table 'buchungen' like this:

ID   Value   Kind   Date
1    200     L      2016-01-01
2    250     B      2016-01-01
3    250     L      2016-01-01
4    200     B      2016-02-01
5    250     B      2016-02-01
6    200     L      2016-02-01

I want to do a query that gives me output like this:

Sum of value, grouped by data and kind.

For January: 450, L and 250 B

For February: 450 B and 200 L

The needed structure:

[{"m":"1","a":"450","b":"250},{"m":"2","a":"200","b":"450}]

With my query:

SELECT MONTH( date ) AS m, SUM( value ) AS a
FROM  `buchungen`
WHERE YEAR( date ) = YEAR( CURDATE( ) )
GROUP BY MONTH( date )

I get the following structure:

[{"m":"4","a":"476852.64"},{"m":"5","a":"851866.74"}]

I have the problem that I don't know how to consider the 'kind' field. How can I do this?

Thanks!

Answer 1

Try this:

SELECT MONTH( date ) AS m, 
       SUM(CASE WHEN Kind = 'L' THEN value ELSE 0 END) AS a,
       SUM(CASE WHEN Kind = 'B' THEN value ELSE 0 END) AS b
FROM  `buchungen`
WHERE YEAR( date ) = YEAR( CURDATE( ) )
GROUP BY MONTH( date )