I have the following table:
Tabela partidas +----+----------+------+---------------------+ | id | name | win | date | +----+----------+------+---------------------+ | 1 | Fulano | Y | 2014-01-01 00:00:00 | | 2 | Fulano | Y | 2014-01-01 00:00:00 | | 3 | Fulano | Y | 2014-01-02 00:00:00 | | 4 | Fulano | Y | 2014-01-03 00:00:00 | | 5 | Fulano | N | 2014-01-03 00:00:00 | | 6 | Beltrano | N | 2014-01-01 00:00:00 | | 7 | Beltrano | N | 2014-01-01 00:00:00 | | 8 | Beltrano | N | 2014-01-02 00:00:00 | | 9 | Beltrano | Y | 2014-01-03 00:00:00 | | 10 | Cicrano | Y | 2014-01-03 00:00:00 | | 11 | Cicrano | N | 2014-01-03 00:00:00 | +----+----------+------+---------------------+
I would like to do something like:
SELECT name, count(win='Y') AS vitorias, count(win='N') AS derrotas
FROM partidas GROUP BY name;
Obviously I get an unwanted answer, because the count does not recognize the command I invented:
+----------+----------+----------+ | name | vitorias | derrotas | +----------+----------+----------+ | Beltrano | 4 | 4 | | Cicrano | 2 | 2 | | Fulano | 5 | 5 | +----------+----------+----------+
Could someone help you get the right answer?
Thank you!