MySQL: Replace null with 0 in GROUP_CONCAT

Question

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

Answer 1

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