How to translate this SQL query into google sheets to count multiple values-CodePudding

I have a sheet with 3 columns: player_a, player_b, result Where player_a and player_b are normalised strings representing the different players in the tournament, and result is either 'W' or 'L'

I would like to create a sheet with the following data:

player_a, player_b, num wins, num losses, winrate as seen on the screenshot above

In SQL, I would do:


SELECT 
  player_a, 
  player_b, 
  num_wins, num_loss, 
  (num_wins*100/(num_wins num_loss)) as winrate
FROM (
SELECT 
  player_a, 
  player_b, 
  count(case when result = 'W' THEN 1 END) as num_wins, 
  count(case when result = 'L' THEN 1 END) as num_loss
 FROM `scores` 
 GROUP BY player_a, player_b) as grouped_scores;

In Google Sheets I tried:

Query(Sheet1!A3:C, "SELECT A, B, count(case when C = 'W' THEN 1 END), count(case when C = 'L' THEN 1 END)", 0)

But case is not supported in count

So to make this work I ended up doing a first query counting the wins: Query(Sheet1!A3:C, "select A, B, count(I) where C = 'W' group by A, B label count(C) 'num wins'", 1)

Then, for each created row, I manually created a num_losses column and added this formula for each cell below

=IFNA(query(Sheet1!A3:C, "select count(C) where C = 'L' AND A='"&INDIRECT("A"&row())&"' AND B='"&INDIRECT("B"&row())&"' group by A, B label count(C)''", 0), 0)

I then also created a column winrate where I made formulas for each cell to calculate the winrate

This works but I would like to do all of this in a single formula/query to make it more clean and easier to maintain.

Is there a way to translate my SQL query above into google sheets to do what I described?

CodePudding user response：

you can try something alike.

Columns A, B, C has the raw data
formula in cell E2: =SORT(UNIQUE(A2:B),1,1)
formula in cell G2:

=ARRAYFORMULA(BYROW(E2:E&F2:F,LAMBDA(efx,if(efx="",,{COUNTA(IFERROR(FILTER(C:C,(A:A&B:B=efx)*(C:C="W")))),COUNTA(IFERROR(FILTER(C:C,(A:A&B:B=efx)*(C:C="L")))),(COUNTA(IFERROR(FILTER(C:C,(A:A&B:B=efx)*(C:C="W"))))*100)/(COUNTA(IFERROR(FILTER(C:C,(A:A&B:B=efx)*(C:C="W")))) COUNTA(IFERROR(FILTER(C:C,(A:A&B:B=efx)*(C:C="L")))))}))))

CodePudding user response：

Given player A,player B,result in A1:Cx of a sheet, the following formula (placed to the side, e.g. in E1) generates the required data in a single formula:

=arrayformula(lambda(a,lambda(b,{split(index(b,,1),"|"),filter(b,{0,1,1,1})})(
query(if(a="",0,a),"select Col1,Col2,Col3,Col3/(Col2 Col3) label Col2 'Losses', Col3 'Wins', Col3/(Col2 Col3) 'Win rate' format Col3/(Col2 Col3) '0%'")))(
query({A1:A&"|"&B1:B,C1:C,C1:C},"select Col1,count(Col2) where Col2 is not null group by Col1 pivot Col3",1)))

N.B. 'Losses' precede 'Wins' because the QUERY pivot clause sorts in alphabetical order - it's possible to flip them round but it makes the formula more complex and I'm considering the order to be a cosmetic concern here.