select - MySQL: Sum and group multiple fields by date + other field -

select - MySQL: Sum and group multiple fields by date + other field -

i have table 3 columns shown below. summarize cost each type (only 0/1/2), , grouping month , year (of date field), using select statement.

+------------+------+-------+ | date | type | cost | +------------+------+-------+ | feb-1-2003 | 0 | 19.40 | | feb-5-2010 | 1 | 28.10 | | mar-3-2011 | 2 | 64.20 | | sep-8-2012 | 0 | 22.60 | | dec-6-2013 | 1 | 13.50 | +------------+------+-------+

the output like:

+----------+------------+------------+------------+ | period | type0 | type1 | type2 | +----------+------------+------------+------------+ | jan 2003 | 123123.12 | 23432.12 | 9873245.12 | | feb 2003 | 123123.12 | 23432.12 | 9873245.12 | | mar 2003 | 123123.12 | 23432.12 | 9873245.12 | etc... +----------+------------+------------+------------+

can help select statement?

mysql not designed pivot queries. it's improve aggregate in other language.. that's not it's impossible, though:

select concat(month(date), ' ', year(date)) period, sum(if(type = 0, cost, 0)) type0, sum(if(type = 1, cost, 0)) type1, sum(if(type = 2, cost, 0)) type2 t1 grouping period

mysql select group