(1) count the number of classes in the week according to teacher_no
-- solution one
SELECT
T1.teacher_no
(CASE WEEK WHEN'1' THEN keshu ELSE 0 END) Monday
(CASE WEEK WHEN'2' THEN keshu ELSE 0 END) Tuesday
(CASE WEEK WHEN'3' THEN keshu ELSE 0 END) Wednesday
FROM
(
SELECT
Teacher_no
WEEK
COUNT (1) AS keshu
FROM teach
GROUP BY teacher_no,WEEK
) T1
GROUP BY t1.teacher_no
The second solution is the function of count () function.
SELECT
T1.teacher_no
(CASE WEEK WHEN'1' THEN keshu ELSE 0 END) Monday
(CASE WEEK WHEN'2' THEN keshu ELSE 0 END) Tuesday
(CASE WEEK WHEN'3' THEN keshu ELSE 0 END) Wednesday
FROM
(
SELECT
T.teacher_no
T.WEEK
COUNT (t.keshu) AS keshu
FROM
(
SELECT
Teacher_no
WEEK
'1' keshu
FROM teach) t
GROUP BY teacher_no,WEEK
) T1
GROUP BY t1.teacher_no
