Google Sheet Query: Return default value to 0 in group by query if a particular group by condition doesn't exist

Question

Not sure if it is possible, however, I am looking if we can do a query in google sheet, which can give the result for the below table as

Month	Value
1	20
4	30
1	10
4	30
1	30

Expected Result:

Month	Value
1	60
2	0
3	0
4	60

As I am trying to create a dynamic dashboard in Google sheets, where a lot of data is being generated for Query to create plot and I would need the above to maintain consistency

Thanks a lot for any help in this regard

Answer 1

This was posted as an answer but was deleted. I'm not sure why, but try this:

=arrayformula(
     query({A:B;split(sequence(max(A:A)-min(A:A)+1,1,min(A:A))&"_0", "_")},
     "select Col1, sum(Col2) group by Col1 
                             order by Col1 label sum(Col2) ''", 0)
)

Answer 2

If you want a script (a custom function, to be precise) it's here:

function make_table(arr) {
  // [[1,...],[4,...],[1,...],[4,...],[1,...]] --> 4
  const latest_month = Math.max(...arr.map(x => x[0]));

  // 4 --> [1,2,3,4]
  const months = new Array(latest_month).fill().map((_,i) => i+1);

  // [1,2,3,4] --> { 1:0, 2:0, 3:0, 4:0 }
  const obj = months.reduce((ar,m) => ({ ...ar, [m]:0 }), {});

  // [[1,20], [4,30], [1,10], [4,30], [1,10]] + obj --> { 1:40, 2:0, 3:0, 4:60 }
  arr.forEach(row => obj[row[0]] += row[1]);

  // { 1:40, 2:0, 3:0, 4:60 } --> [ [1,40], [2,0], [3,0], [4,60] ]
  return Object.keys(obj).sort().map(month => [+month, obj[month]]);
}