Mysql - Counting different values of the same field

Question

I have the following table:

Table games
+----+----------+------+---------------------+
| 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 wins, count(win='N') AS losses FROM games GROUP BY name;

Obviously I get an unwanted response due to the "count" does not recognize the command that I invented:

+----------+----------+----------+
| name     | wins     | losses   |
+----------+----------+----------+
| Beltrano |        4 |        4 |
| Cicrano  |        2 |        2 |
| Fulano   |        5 |        5 |
+----------+----------+----------+

Could someone help get the correct answer?

hjpotter92 · Accepted Answer · 2014-08-09 12:06:22Z

4

SELECT
    name,
    SUM( win = 'Y' ) AS wins,
    SUM( win = 'N' ) AS losses
FROM games
GROUP BY name;

answered Aug 9, 2014 at 12:06

hjpotter92

81.1k36 gold badges148 silver badges188 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

In MySQL, the simpler expression is just sum(win = 'Y').

Collectives™ on Stack Overflow

Mysql - Counting different values of the same field

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related