I have a table called trx
trx_year trx_month Product number_of_trx
2018 4 A 100
2018 5 A 300
2018 3 A 500
2018 1 A 200
2018 2 A 150
2018 5 B 400
2018 2 B 200
2018 1 B 350
I want the result:
Product with the number of trx that order by month asc
I have a query like this:
select product,GROUP_CONCAT(number_of_trx order by trx_month)
from trx
where trx_year=2018
group by product
The Result of that query:
Product Data
A 200,150,500,100,300
B 350,200,400
But, I want Result like this: (that the null value of the month replaced by 0)
Product Data
A 200,150,500,100,300
B 350,200,0,0,400
I already try ifnull() and coalesce() like this: (but the result is same as before)
select product,GROUP_CONCAT(ifnull(number_of_trx,0) order by trx_month)
from trx
where trx_year=2018
group by product;
select product,GROUP_CONCAT(coalesce(number_of_trx,0) order by trx_month)
from trx
where trx_year=2018
group by product;
maybe you can help me, please check http://sqlfiddle.com/#!9/f1ed4/3
This is what I came up with. Probably could be more efficient but you can get ideas from it. Join to product table instead of selecting distinct products. Also expand to include months beyond 5.
SELECT trx2.product, GROUP_CONCAT(trx2.total order by trx2.trx_month)
FROM
(SELECT temp2.product, temp2.trx_month, SUM(temp2.number_of_trx) AS total
FROM
(SELECT products.product, temp1.trx_month, temp1.number_of_trx
FROM (select 1 as trx_month, 0 as number_of_trx
UNION select 2, 0
UNION select 3, 0
UNION select 4, 0
UNION select 5, 0) as temp1,
(SELECT distinct product from trx) AS products
UNION ALL
SELECT trx.product, trx.trx_month, trx.number_of_trx
FROM trx) as temp2
GROUP BY temp2.product, temp2.trx_month) AS trx2
GROUP BY product