Sort/Order query result based on calculated fields-CodePudding

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:

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

CodePudding user response：

You should fill the blanks with 0.

=SORT(QUERY(query(ArrayFormula({Transactions!B:B,IF(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 (Col2 is not NULL or Col3 is not Null) group by Col1",1),"select Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col2 Col5, Col3 Col6, Col3*Col4 Col6*Col7",1),10,FALSE)