Sort/Order query result based on calculated fields

Question

I have a list of transactions in Transactions tab and in Summary I would like to summarize by tickers the performance. I am using query for grouping the data and using aggregate functions to calculate %-Win, %-Lost (see the link at the bottom with the sample spreadsheet):

Transaction tab:

=query({Transactions!B:B,Transactions!C:F}, 
  "select Col1, count(Col2),sum(Col4),
  (count(Col2)/(count(Col2)+count(Col3))), count(Col3),
  sum(Col5), 
  (count(Col3)/(count(Col3)+count(Col2))) where Col1 is not NULL 
  and 
     (Col2 is not NULL or Col3 is not Null) 
  group by Col1 
  label count(Col2) 'Win', sum(Col4) '$-Win', 
    (count(Col2)/(count(Col2)+count(Col3))) '%-Win', 
    count(Col3) 'Lost', sum(Col5) '$-Lost', 
    (count(Col3)/(count(Col3)+count(Col2))) '%-Lost'",1)

Sample of Summary tab:

but I was not able to obtain from the query by ticker: Total Transactions, Net Gains, Exp. Value(Expected Value), so I did use Arrayformula, and it works, the problem is that I am not able to sort the result by expected value nor Net Gain (FUBO should be first). I was able to calculate percentage using a combination of aggregated functions, but not for the above additional calculations directly in the query.

I tried to use query clause order by: sum(Col3)+sum(Col5) (Net gains) but it doesn't work, it only returns a value when there are Win and Lost transactions.

Using Data->Sort Range doesn't provide the expected result either. Because there are different sources of data: the query and the result of Arrayformula.

I guess I would need to obtain all required calculated fields directly from the query and then to order by, or to find a way to sort the result combining the query and Arrayformula results. The clause order by works well for aggregated functions that are present in the select elements, but not when the sorting should happen based on a formula based on calculated columns.

Here you can find a sample file from my real situation:

https://docs.google.com/spreadsheets/d/1xrDSWGJVIsWD6fvAOdMOZkw2rEY9lGPZRb_Ww_nC7YQ/edit?usp=sharing

Note: A possible solution would be to combine all the results into one sort statement, but I am not able to make it work

=sort({
query({Transactions!B2:B,Transactions!C2:F}, "select Col1, count(Col2),sum(Col4), (count(Col2)/(count(Col2)+count(Col3))), count(Col3), sum(Col5), (count(Col3)/(count(Col3)+count(Col2))) where Col1 is not NULL and (Col2 is not NULL or Col3 is not Null) group by Col1 label count(Col2) '', sum(Col4) '', (count(Col2)/(count(Col2)+count(Col3))) '', count(Col3) '', sum(Col5) '', (count(Col3)/(count(Col3)+count(Col2))) ''",0),
ARRAYFORMULA(if(not(ISBLANK(A2:A)), B2:B+E2:E,)),
ARRAYFORMULA(if(not(ISBLANK(A2:A)), C2:C+F2:F,)),
ARRAYFORMULA(if(not(ISBLANK(A2:A)), (C2:C)*(D2:D) + (F2:F)*(G2:G),))
},10, FALSE)

In the same way avoiding using Arrayformula using two query statements, doesn't work:

=sort({
query({Transactions!B2:B,Transactions!C2:F}, "select Col1, count(Col2),sum(Col4), (count(Col2)/(count(Col2)+count(Col3))), count(Col3), sum(Col5), (count(Col3)/(count(Col3)+count(Col2))) where Col1 is not NULL and (Col2 is not NULL or Col3 is not Null) group by Col1 label count(Col2) '', sum(Col4) '', (count(Col2)/(count(Col2)+count(Col3))) '', count(Col3) '', sum(Col5) '', (count(Col3)/(count(Col3)+count(Col2))) ''",0),
query(query({Transactions!B2:B,Transactions!C2:F}, "select Col1, count(Col2),sum(Col4), (count(Col2)/(count(Col2)+count(Col3))), count(Col3), sum(Col5), (count(Col3)/(count(Col3)+count(Col2))) where Col1 is not NULL and (Col2 is not NULL or Col3 is not Null) group by Col1 label count(Col2) '', sum(Col4) '', (count(Col2)/(count(Col2)+count(Col3))) '', count(Col3) '', sum(Col5) '', (count(Col3)/(count(Col3)+count(Col2))) ''",0),"select Col2+Col5 label Col2+Col5 ''",0),
query(query({Transactions!B2:B,Transactions!C2:F}, "select Col1, count(Col2),sum(Col4), (count(Col2)/(count(Col2)+count(Col3))), count(Col3), sum(Col5), (count(Col3)/(count(Col3)+count(Col2))) where Col1 is not NULL and (Col2 is not NULL or Col3 is not Null) group by Col1 label count(Col2) '', sum(Col4) '', (count(Col2)/(count(Col2)+count(Col3))) '', count(Col3) '', sum(Col5) '', (count(Col3)/(count(Col3)+count(Col2))) ''",0), "select Col3+Col6 label Col3+Col6 ''",0),
query(query({Transactions!B2:B,Transactions!C2:F}, "select Col1, count(Col2),sum(Col4), (count(Col2)/(count(Col2)+count(Col3))), count(Col3), sum(Col5), (count(Col3)/(count(Col3)+count(Col2))) where Col1 is not NULL and (Col2 is not NULL or Col3 is not Null) group by Col1 label count(Col2) '', sum(Col4) '', (count(Col2)/(count(Col2)+count(Col3))) '', count(Col3) '', sum(Col5) '', (count(Col3)/(count(Col3)+count(Col2))) ''",0), "select Col3*Col4+Col6*Col7 label Col3*Col4+Col6*Col7 ''",0)
},10, FALSE)

Doesn't give all the result values for Net Gain and Exp. Value

As you can see it only provides Net Gains and Exp. Value where are Win and Lost values on the same row.

Answer 1

You should fill the blanks with 0.

=SORT(QUERY(query(ArrayFormula({Transactions!B:B,
 IF(Transactions!C:F="",0, Transactions!C:F)}), 
  "select  Col1, sum(Col2),sum(Col4),
    (sum(Col2)/(sum(Col2)+sum(Col3))), 
    sum(Col3), sum(Col5), (sum(Col3)/(sum(Col3)+sum(Col2))) 
    where  Col1 is not NULL and NOT (Col2 = 0 and Col3 = 0) group by Col1",1),
 "select Col1, Col2, Col3, Col4, Col5, Col6, Col7,Col2+Col5, 
    Col3+Col6,Col3*Col4+Col6*Col7 
    label Col2 'Win',Col3 '$-Win', Col4 '%-Win', Col5 'Lost', 
    Col6 '$-Lost', Col7 '%-Lost', Col2+Col5 'Total Transactions', 
    Col3+Col6 'Net Gains',Col3*Col4+Col6*Col7 'Exp. Value'",1),
10,FALSE)

Notes:

1. The condition: NOT (Col2 = 0 and Col3 = 0)ensures to exclude transactions that were not sold, i.e. Win =0 and Lost = 0
2. The condition: IF(Transactions!C:F="",0, Transactions!C:F)ensures empty values are replaces by 0to ensure the agregate SQL functions work as expected