mercredi 26 août 2015

Sqlite percentage using GROUP BY

I have some data which contains "Currency Pair" (like EUR/USD,SPX500/USD etc) and Pl among other info. I can obtain the wins using

    SELECT "Currency Pair", COUNT(Pl) AS Win FROM data  WHERE Pl>0.01
    GROUP BY "Currency Pair";

while the total number of operations is given by

    SELECT "Currency Pair", COUNT(Pl) AS Total FROM data  WHERE ABS(Pl)>0.01
    GROUP BY "Currency Pair";

Now I would like to print a table like

    Currency Pair     Win   Tot   Success rate
    ---------------   ---   ---   ------------
    EUR/USD             3    11   27.27
    SPX500/USD          6     8   75.00

But unfortunatly I'm not able to correctly calculate the percentage and neither to print Win and Tot on the same table. My main problem is to pass two different WHERE.

Aucun commentaire:

Enregistrer un commentaire