Multiple SELECT With Multiple WHERE

Question

I am stumped and need your help.

I am trying to select three columns from my table where one of them is a VARCHAR and the other two are either "Y" or "N".

SELECT CurrencyPair, Count(Accepted) As Accepted, Count(Successful) As Successful
 FROM Trades
 WHERE Accepted = "Y"
 GROUP BY CurrencyPair
 ORDER BY Successful/Accepted DESC, Successful DESC
 LIMIT 5 

This returns a table just as I like in regards to format, but the data seems to only show where the three columns coincide. For example id three were accepted and two were successful I would expect the count for accepted to be 3 and the count for successful to be 2. My table always returns the same number of accepts and successes despite knowing the data in the table having more accepted than successes.

Furthermore, please have a look at the division operator to make sure it is correct syntax. It's purpose is to place higher success ratios at top.

Thanks in advance.

• Gaurava Agarwal: Please post create table statement for your table for others to help you fast.

Answer 1

Try to use case inside count:

Count(Case WHEN Successful = 'Y' THEN 1 ELSE 0 END) 

Regarding division operator, looks ok, if you want to order by count.