## Saturday, September 5, 2015

### Google Sheet Query involving counting with differences between columns

Question:

Here's a sample of what the sheet looks like:

A   B    C
1    0    Win
2    0    Win
3    0    Lose
4    4    Lose
5    0    Win
3    2    Lose
2    0    Lose
0    1    Win
1    2    Lose
I want to figure out what the percentage of wins to losses are based on the difference between A and B. So the result should be:
Diff   Percentage
5       1
3       0
2       0.5
1       0.5
0       0
-1      0.5
The query in normal SQL looks like this:
select A-B as Diff, cast(sum(case when C = 'Win' then 1 else 0 end) as float) / cast(count(*) as float) as Percentage from test group by A-B;
I, however, don't know how to do case in sheets queries, and I've read that you can't. I could figure out how to get the differences and the number of times the difference occurred, but I can't seem to figure out how to count the different outcomes in column C. In SQL I would use a case and condition on the possible values.
If someone could help me figure this out, that'd be great. I'd also appreciate it if you could explain how the final query works so I can follow along and figure out where I went wrong. Thanks.

Solution:

Have a look at the following screenshot:

I have the data in columns A,B and C.
Now we need to generate an extra column in which we have "1" corresponding the third column having value "Win".
So I have the formula in cell F1:
=arrayformula({A:C,if(C:C="Win",1,0)})

and as you can see we have generated the column I with the help of above formula. To avoid the "0" where there is no value in first column, we have the following formula in cell L1:
=query(arrayformula({A:C,if(C:C="Win",1,0)}),"select * where Col1 is not null")

Now have a look at the following screenshot:

I have the following formula in cell E1:
=query(arrayformula({A:C,if(C:C="Win",1,0)}),"select Col1-Col2,sum(Col4),count(Col3) where Col1 is not null group by Col1-Col2 order by Col1-Col2 desc label Col1-Col2 '',sum(Col4) '',count(Col3) ''")

and now we can divide the 2nd column (F) generated by 3rd column (G) generated.

So finally we have:

I have the following formula in cell E1:
=query(arrayformula({A:C,if(C:C="Win",1,0)}),"select Col1-Col2,(sum(Col4)/count(Col3)) where Col1 is not null group by Col1-Col2 order by Col1-Col2 desc label Col1-Col2 '',(sum(Col4)/count(Col3)) ''")