Unit 3 · GROUP BY, COUNT, SUM, AVG, MIN/MAX, HAVING

Data file / 資料檔：student table for the worked examples; ClassWork 3 uses an extended scores file (with Subject and Gender columns) — download link shared in class.

Start Here / 開始做題

1. Decide which column you are grouping by (usually the "category" column, e.g. Class). 中文：先決定要依哪一欄分組。
2. Decide which aggregate function you need: COUNT, SUM, AVG, MIN, MAX. 中文：再決定要用哪個聚合函式。
3. If you need to filter groups (not rows), use HAVING, not WHERE. 中文：篩選「分組後」的結果要用 HAVING，不是 WHERE。

Unit Dashboard / 單元地圖

Quick Concepts / 重點

SQL Syntax / SQL 語法

SELECT Class, COUNT(*) AS student_count, AVG(Grade) AS avg_score
FROM student
GROUP BY Class
HAVING AVG(Grade) > 70;

Yellow Syntax Guide / 黃色語法重點

Dry Run / 手算追蹤

Sample rows (illustrative only / 僅供示範):

Grouped by Class: F6A → count 2, avg 80; F6B → count 2, avg 55. After HAVING AVG(Grade) > 70, only F6A remains. 中文：先分組計算每班人數與平均分，再用 HAVING 篩掉平均分不足 70 的班級。

Examples / 例題

Example 3-1 · Count per Group

題目說明 / Problem Statement

Show how many students are in each class. 中文：計算每個班級有多少學生。

Reference Answer / 參考答案

SELECT Class, COUNT(*) AS student_count
FROM student
GROUP BY Class;

Example 3-2 · Sum per Group

題目說明 / Problem Statement

Show the total grade score for each class. 中文：計算每個班級的總分數。

Reference Answer / 參考答案

SELECT Class, SUM(Grade) AS total_score
FROM student
GROUP BY Class;

Example 3-3 · Average per Group

題目說明 / Problem Statement

Show the average grade for each class. 中文：計算每個班級的平均分數。

Reference Answer / 參考答案

SELECT Class, AVG(Grade) AS avg_score
FROM student
GROUP BY Class;

Example 3-4 · Min and Max per Group

題目說明 / Problem Statement

Show the highest and lowest grade for each class. 中文：計算每個班級最高分與最低分。

Reference Answer / 參考答案

SELECT Class, MAX(Grade) AS max_grade, MIN(Grade) AS min_grade
FROM student
GROUP BY Class;

Example 3-5 · Filter Groups with HAVING

題目說明 / Problem Statement

Show only classes whose average grade is above 70. 中文：找出平均分數大於 70 的班級。

Reference Answer / 參考答案

SELECT Class, AVG(Grade) AS avg_score
FROM student
GROUP BY Class
HAVING AVG(Grade) > 70;

ClassWork 3 / 課後練習

This file adds Subject and Gender columns to the student data. Check the actual column names in your downloaded file before writing your query. 中文：本次資料檔多了「科目」與「性別」欄位，執行前請先核對實際欄位名稱。

ClassWork 3-1 · Count Students per Class

Count students per class. 中文：計算每個班級的學生人數。

SELECT Class, COUNT(*) AS student_count
FROM student
GROUP BY Class;

ClassWork 3-2 · Average Score by Subject (Filtered)

Average score by subject, only keeping subjects with an average ≥ 75. 中文：計算各科平均分，僅保留平均分 ≥ 75 的科目。

SELECT Subject, AVG(Grade) AS avg_score
FROM student
GROUP BY Subject
HAVING AVG(Grade) >= 75;

ClassWork 3-3 · Total Score per Class per Subject

Show the total score for each class within each subject (no subquery required) — display subject, class, total score. 中文：計算每科別中，各班級的總分；顯示科目、班級、總分（供比較用）。

SELECT Subject, Class, SUM(Grade) AS total_score
FROM student
GROUP BY Subject, Class
ORDER BY Subject, total_score DESC;

ClassWork 3-4 · Gender Distribution per Class

Show how many male (M) and female (F) students are in each class. 中文：各班級的性別人數分佈（M/F 各有多少人）。

SELECT Class, Gender, COUNT(*) AS count
FROM student
GROUP BY Class, Gender
ORDER BY Class, Gender;

ClassWork 3-5 · Classes with Many High Scorers

Find classes with at least 5 students scoring ≥ 85, ordered by that count from high to low. 中文：找出「高分（≥85）人數 ≥ 5」的班級，並按人數由高到低排序。

SELECT Class, COUNT(*) AS high_score_count
FROM student
WHERE Grade >= 85
GROUP BY Class
HAVING COUNT(*) >= 5
ORDER BY high_score_count DESC;