SQL 資料分析教學：DAY 7

 SQL範例：分組統計學生資料

使用students資料表，透過GROUP BY與彙總函數計算每個班級學生人數與平均年齡。這種查詢常用於製造業生產線人員統計或品質資料分組分析。

​在MYSQL新增練習資料

CREATE TABLE students ( id INT PRIMARY KEY, name VARCHAR(50), class VARCHAR(10), age INT, score INT ); INSERT INTO students VALUES (1, '張小明', 'A班', 20, 85), (2, '李小華', 'A班', 19, 92), (3, '王小強', 'A班', 21, 78), (4, '陳小美', 'B班', 20, 88), (5, '劉小芳', 'B班', 19, 95), (6, '吳小勇', 'B班', 22, 82), (7, '鄭小蘭', 'C班', 18, 90), (8, '錢小智', 'C班', 20, 87), (9, '孫小玉', 'C班', 19, 93), (10, '周小軍', 'A班', 21, 80), (11, '吳小文', 'B班', 20, 89), (12, '鄭小峰', 'A班', 19, 91), (13, '錢小雪', 'C班', 21, 84), (14, '新學生X', 'D班', 18, 76), (15, '新學生Y', 'D班', 20, 88), (16, '林小豪', 'A班', 22, 79), (17, '黃小琪', 'B班', 19, 94), (18, '徐小偉', 'C班', 20, 86), (19, '郭小雯', 'A班', 21, 83), (20, '蔡小龍', 'D班', 19, 81);

-- 昨日分組統計查詢（MySQL完全支援） SELECT class, COUNT(*) AS student_count, ROUND(AVG(age), 2) AS avg_age FROM students GROUP BY class ORDER BY student_count DESC;

查詢語法：

SELECT class, COUNT(*) AS student_count, AVG(age) AS avg_age

FROM students

GROUP BY class

ORDER BY student_count DESC;

解說與應用： COUNT(*)計算人數，AVG(age)求平均值，按人數降序排序。搭配WHERE條件可篩選特定班級，例如WHERE age > 18，提升資料分析效率。