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

but I got

Please advise me to get through this.

Thank you.

CodePudding user response：

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

DEMO Fiddle

CodePudding user response：

SUGGESTION ( using PL/SQL ! ) :

CREATE  PROCEDURE countCourses(OUT bothtype INT,OUT Inpersononly INT,OUT Onlineonly INT)
begin
SELECT COUNT(*) INTO bothtype FROM Course;
select COUNT(*) INTO Inpersononly FROM Course
WHERE courseType = "In person";
select  COUNT(*) INTO Onlineonly FROM Course
WHERE courseType = "Online";
end;

call countCourses(@bothtype,@Inpersononly,@Onlineonly);
SELECT @bothtype,@Inpersononly,@Onlineonly;

EXPLICATION :

• Creating procedure to store the count of each type of course in OUT variable
• Call the procedure with convenient parameters
• Select out given parameters