CODEWITHSUNDEEP
loopsgoogle-apps-scriptalasql
user3447653
user3447653

Reputation: 4158

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

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.

Upvotes: 0

Views: 933

Answers (1)

Krzysztof Dołęgowski
Krzysztof Dołęgowski

Reputation: 2660

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")

enter image description here

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)))

enter image description here

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.

enter image description here

Upvotes: -1

Related Questions