MySQL: group occurrences per date

Question

I guess this is a recurring question but I haven't found the right topic to point me in the right direction.

I have chat table which pretty much looks like:

+---------+----------------+---------------------+-----------------+
| id(int) | author(string) |   date(datetime)    | message(string) |
+---------+----------------+---------------------+-----------------+
|       1 | John           | 2016-01-01 17:18:00 | I               |
|       2 | Mary           | 2016-01-01 14:22:00 | Just            |
|       3 | John           | 2016-01-01 09:02:00 | Want            |
|       4 | John           | 2016-01-02 17:18:00 | To              |
|       5 | Mary           | 2016-01-03 18:26:00 | Say             |
|       6 | John           | 2016-01-03 10:42:00 | Hello           |
+---------+----------------+---------------------+-----------------+

What I would like to get:

+------------+------+------+
|    day     | Mary | John |
+------------+------+------+
| 2016-01-01 |    1 |    2 |
| 2016-01-02 |    0 |    1 |
| 2016-01-03 |    1 |    1 |
+------------+------+------+

Am I obligated to do a subquery in the COUNT statement ?

So far I came up with:

SELECT DATE(date) as day,
(SELECT COUNT(id) FROM chat WHERE author = 'Mary') AS 'Mary'
(SELECT COUNT(id) FROM chat WHERE author = 'John') AS 'John'
FROM chat
GROUP BY day
ORDER BY day ASC

But this is giving me the total message count per author at each row:

+------------+------+------+
|    day     | Mary | John |
+------------+------+------+
| 2016-01-01 |    2 |    4 |
| 2016-01-02 |    2 |    4 |
| 2016-01-03 |    2 |    4 |
+------------+------+------+

Answer 1

Just use conditional aggregation:

SELECT DATE(date) as day,
       SUM(author = 'Mary') AS Mary,
       SUM(author = 'John') AS John
FROM chat
GROUP BY day
ORDER BY day ASC