| Course name | Section number | Course type |
|---|---|---|
| MATH 101 | 1 | In person |
| MATH 101 | 2 | In person |
| MATH 101 | 3 | Online |
| MATH 101 | 4 | In person |
| SOC 101 | 1 | In person |
| SOC 101 | 2 | In person |
| SOC 101 | 3 | In person |
| ENGL 201 | 1 | In person |
| ENGL 201 | 2 | Online |
| ENGL 201 | 3 | Online |
| ENGL 201 | 4 | In person |
| PHY 101 | 1 | Online |
| PHY 101 | 2 | Online |
From this table, I'd like to count Courses with only an 'In person' course, an 'Online' course, and both course types.
The query I tried is below.
SELECT
SUM(CASE WHEN coursetype = 'Inperson' AND coursetype = 'Online' THEN 1 ELSE 0 END) AS bothtype,
SUM(CASE WHEN coursetype = 'Online' THEN 1 ELSE 0 END) AS Onlineonly,
SUM(CASE WHEN coursetype = 'Inperson' THEN 1 ELSE 0 END) AS Onlineonly
From Course
The result what I expected is
| bothtpye | Onlineonly | Inpersononly |
|---|---|---|
| 2 | 1 | 1 |
but I got
| bothtpye | Onlineonly | Inpersononly |
|---|---|---|
| 0 | 7 | 6 |
Please advise me to get through this.
Thank you.
My solution uses double conditional aggregation.
SELECT SUM (CASE WHEN In_Person > 0 AND Online > 0 THEN 1 ELSE 0 END) as bothtype,
SUM (CASE WHEN In_Person > 0 AND Online = 0 THEN 1 ELSE 0 END) as inpersononly,
SUM (CASE WHEN In_Person = 0 AND Online > 0 THEN 1 ELSE 0 END) as onlineonly
FROM (
SELECT Course_name,
SUM(CASE WHEN Course_type='In Person' THEN 1 ELSE 0 END) as In_Person,
SUM(CASE WHEN Course_type='Online' THEN 1 ELSE 0 END) as Online
FROM Course
GROUP BY Course_name
) tot