You are attempting to pivot the data from rows into columns. MySQL does not have a pivot function but you can use an aggregate function with a CASE to get the result:
select product,
sum(case when month(date) = 12 and year(date) = 2012
then qty else 0 end) Dec2012,
sum(case when month(date) = 1 and year(date) = 2013
then qty else 0 end) Jan2013
from yourtable
group by product
See SQL Fiddle with Demo.
This can also be written using a subquery to get the date in the format of Month-year:
select product,
sum(case when MonthYear = 'Dec_2012' then qty else 0 end) Dec2012,
sum(case when MonthYear = 'Jan_2013' then qty else 0 end) Jan2013
from
(
select product,
date_format(date, '%b_%Y') MonthYear,
qty
from yourtable
) src
group by product;
See SQL Fiddle with Demo.
Then if you wanted to generate a list of dates dynamically or will have an unknown number of dates that you want to return, you can use a prepared statement to generate dynamic SQL:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(case when MonthYear = ''',
MonthYear,
''' then qty else 0 end) AS ',
MonthYear
)
) INTO @sql
FROM
(
select product,
date_format(date, '%b_%Y') MonthYear,
qty
from yourtable
) src;
SET @sql = CONCAT('SELECT product, ', @sql, '
from
(
select product,
date_format(date, ''%b_%Y'') MonthYear,
qty
from yourtable
) src
GROUP BY product');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
See SQL Fiddle with Demo. All three will give you the result:
| PRODUCT | DEC_2012 | JAN_2013 |
------------------------------------
| reference1 | 4 | 0 |
| reference2 | 2 | 9 |
| reference3 | 0 | 6 |
