Mysql - Count different values from the same field

Asked

Viewed 3,714 times

1

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’d 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 response because Count does not recognize the command I invented:

+----------+----------+----------+
| name     | vitorias | derrotas |
+----------+----------+----------+
| Beltrano |        4 |        4 |
| Cicrano  |        2 |        2 |
| Fulano   |        5 |        5 |
+----------+----------+----------+

Could someone help get the right answer?

Thank you very much!

mysql database

1 answer

3


You can do with the SUM():

SELECT name, sum(win='Y') AS vitorias, sum(win='N') AS derrotas
FROM partidas GROUP BY name;

Ex:

inserir a descrição da imagem aqui

Browser other questions tagged mysql database

You are not signed in. Login or sign up in order to post.