Group by and filter based on sum of a column in google apps script

Question

I am trying to group by vendor and id and take the sum of total weight from the below table:

 Vendor Id  Weight
  AAA   1   1234
  AAA   1   121
  AAA   2   5182
  BBB   1   311 
  BBB   1   9132
  BBB   2   108

In the below query, variable 'row' is the input table

I have the below query that groups by Vendor and Id and sums Weight

var res_2 = alasql('SELECT [0] as Vendor,[1] as Id, sum([2]) as Total_Weight FROM ? GROUP BY [0], [1]',[row]);  

Result is as follows:

[ { Vendor: 'AAA', Id: '1', Total_Weight: 1355 },
  { Vendor: 'AAA', Id: '2', Total_Weight: 5182 },
  { Vendor: 'BBB', Id: '1', Total_Weight: 9443 },
  { Vendor: 'BBB', Id: '2', Total_Weight: 108 }, ]

My next part is I need to loop over this array and for every unique vendor, I need to take the maximum 'Total_Weight' and get the corresponding 'Id' and push the variables 'Vendor' and 'Id' to another array.

Hence, the results has to be

[{Vendor: 'AAA', Id: '2'},{Vendor: 'BBB', Id: '1'}]

Can anyone guide me on whether this could be accomplished through a logic or do I need to modify the query as such. Any suggestions would be appreciated.

Answer 1

I see you put a tag google-sheets here. I think your problem can be solved within this tool:

First stage you get using query formula:

=query(B2:D,"select B, C, sum(D) where B is not null group by C, B order by sum(D) desc")

As you have your table sorted by max sum value, you can use vlookup function and take first row for the vendor you need and build a table:

=ArrayFormula(ifna(vlookup(unique(F5:F),F4:H8,{1,2},false)))

Or you can do both stages together (and use query as a table inside vlookup)

=ArrayFormula(ifna(vlookup(unique(B3:B),query(B3:D,"select B, C, sum(D) where B is not null group by C, B order by sum(D) desc"),{1,2},false)))

The result is an array with 2 columns - vendor and ID corresponding to max sum.