2

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

Eru
  • 51
  • 8

5 Answers5

1

Generate all the rows you want using cross join. That would be all product/month combinations. Then use left join to bring in data and group by to condense it:

select p.product,
       group_concat(coalesce(trx.number_of_trx, 0) order by trx_month)
from (select distinct product from trx) p cross join
     (select distinct trx_year, trx_month
      from trx
      where trx_year = 2018
     ) yyyymm left join
     trx
     on trx.product = p.product and
        trx.trx_year = yyyymm.trx_year
        trx.trx_month = yyyymm.trx_month
group by p.product

Note the order by in the group_concat(). This is very important if you want the results in chronological order.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

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
Hassan Voyeau
  • 3,383
  • 4
  • 22
  • 24
0

Group by groups the table contents with available values. FOR Product A: months 1 2 3 4 5 are available and FOR Product B: months 1 2 5 are available

It will not fill table automatically with month 3 and 4 against product B

To resolve this Either you will have to fill this table with B - 3 and B - 4 and 0 value for number of trx OR you can create temp table and do the same in that.

To implement this, 1. you will have to use date function (convert your month and year value to date) 2. Increase month value for each loop, If the month value is not available against current product value, insert record in the table. 3. Then run your group by query on this updated table and it will give you required result.

This is bit complicated task to implement. You will have to do multiple checks.

If I will find any other solution, I will post. Thanks.

Hardik Nuwal
  • 73
  • 8
0

select product,GROUP_CONCAT(number_of_trx) order by trx_month), group_concat(case when trx_year = 2018 then number_of_trx else 0 end)as data from trx group by product;

Shaikh Shifa
  • 1
0

If concat is null then returns 0 else returns values

SET SESSION group_concat_max_len = 10000000;
SELECT coalesce(group_concat(DISTINCT columnname),0) as values

Sreejith N
  • 25
  • 5