Query with group by date

Question

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!

George Joseph · Accepted Answer · 2020-07-06 16:47:43Z

1

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

answered Jul 6, 2020 at 16:47

George Joseph

5,93213 silver badges25 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2020-07-06 16:47:10Z

1

Use conditional aggregation:

SELECT
    creation_date,
    COUNT(CASE WHEN role = 2 THEN 1 END) AS totalInstructors,
    COUNT(CASE WHEN role = 3 THEN 1 END) AS totalStudents
FROM yourTable
GROUP BY
    creation_date;

Demo

answered Jul 6, 2020 at 16:47

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Comments

nbk · Accepted Answer · 2020-07-06 16:58:42Z

1

A simple GRoupBY and SUM helps

This works. because the compasison role = 2 gives 1 back it it tue and 0 if it is false

CREATE TABLE table1 (
  `name` VARCHAR(6),
  `role` INTEGER,
  `creation_date` VARCHAR(10)
);

INSERT INTO table1
  (`name`, `role`, `creation_date`)
VALUES
  ('Tom', '2', '2020-07-01'),
  ('Diana', '3', '2020-07-01'),
  ('Rachel', '3', '2020-07-01'),
  ('Michel', '3', '2020-08-01');

SELECT
    SUM(`role` = 2) totalInstructors ,
    SUM(`role` = 3) totalStudents,
   `creation_date`
FROM table1
GROUP BY `creation_date`
ORDER BY `creation_date`

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

db<>fiddle here

edited Jul 6, 2020 at 16:58

answered Jul 6, 2020 at 16:51

nbk

49.6k8 gold badges32 silver badges53 bronze badges

1 Comment

Tom Lima Over a year ago

Is a good approach to the problem, it worked very well! thank you!

Jim Macaulay · Accepted Answer · 2020-07-06 16:48:56Z

0

Please use below query,

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

You can use COALESCE() to replace null with 0

select COALESCE(totalInstructors, 0) as totalInstructors, COALESCE(totalStudents, 0) as totalStudents,creation_date
from
(select 
case when role = 2 then count(1) end as totalInstructors,
case when role = 3 then count(1) end as totalStudents,
creation_date
from table_name
group by  creation_date) qry;

answered Jul 6, 2020 at 16:48

Jim Macaulay

5,2515 gold badges32 silver badges60 bronze badges

Collectives™ on Stack Overflow

Query with group by date

4 Answers 4

Comments

Demo

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related