Query with group by date

I’m working on the following user table, where role = 2 means the user is an instructor, and role = 3 means that the user is a student.

+--------+------+---------------+
|  name  | role | creation_date |
+--------+------+---------------+
| Tom    |    2 | 2020-07-01    |
| Diana  |    3 | 2020-07-01    |
| Rachel |    3 | 2020-07-01    |
| Michel |    3 | 2020-08-01    |
+--------+------+---------------+

My goal is to select the sum value of all instructors and students, grouped by date. The result should look like this:

+------------------+---------------+---------------+
| totalInstructors | totalStudents | creation_date |
+------------------+---------------+---------------+
|                1 |             2 | 2020-07-01    |
|                0 |             1 | 2020-08-01    |
+------------------+---------------+---------------+

In this case, on 2020-07-01, I had 1 instructor and 2 students registered and on 2020-08-01, I had no instructors and I had 1 student registered.

My problem is that I am having difficulties in setting up this query, if someone can help me thank you very much!

Answer

You would need count with a case statement as follows

select count(case when role=2 then 1 end) as totalInstructors 
      ,count(case when role=3 then 1 end) as totalStudents 
      ,creation_date 
  from tbl
group by creation_date