上一篇介紹的內建函數，是對查詢結果中的欄位值做運算，產生更有意義的值。而本文將介紹「聚合函數」，它的用途在於統計，例如加總、平均與計數。可聯想成將所有查詢結果的特定欄位值聚集起來做計算。

另外也會介紹分組查詢，根據欄位值進行分組，再搭配聚合函數做統計。最後提供一些例題。

此篇亦轉載到個人部落格。

一、測試資料

本文使用的測試資料，參考自此影片：
https://www.youtube.com/watch?v=gvRXjsrpCHw&t=8198s

部門資料表。

CREATE TABLE `dept` (
    `dept_id` INT PRIMARY KEY,
    `dept_name` VARCHAR(20)
);

INSERT INTO `dept` (`dept_id`, `dept_name`)
VALUES (1, "研發"), (2, "行政"), (3, "資訊");

員工資料表，其中包含部門編號。

CREATE TABLE `employee` (
    `emp_id` INT PRIMARY KEY,
    `name` VARCHAR(20),
    `birthday` DATE,
    `gender` VARCHAR(1),
    `salary` INT,
    `dept_id` INT
);

INSERT INTO `employee` (`emp_id`, `name`, `birthday`, `gender`, `salary`, `dept_id`)
VALUES
(206, "小黃", "1998-10-08", "F", 50000, 1),
(207, "小綠", "1985-09-16", "M", 29000, 2),
(208, "小黑", "2000-12-19", "M", 35000, 3),
(209, "小白", "1997-10-22", "F", 39000, 3),
(210, "小蘭", "1925-09-10", "F", 84000, 1);

客戶資料表。

CREATE TABLE `client` (
    `client_id` INT PRIMARY KEY,
    `client_name` VARCHAR(20)
);

INSERT INTO `client` (`client_id`, `client_name`)
VALUES
(400, "阿狗"),
(401, "阿貓"),
(402, "旺來"),
(403, "露西"),
(404, "艾瑞克");

銷售資料表。其中包含員工編號與客戶編號。

CREATE TABLE `sales` (
    `emp_id` INT,
    `client_id` INT,
    `total_sales` INT
);

INSERT INTO `sales` (`emp_id`, `client_id`, `total_sales`)
VALUES
(206, 400, 70000),
(207, 401, 24000),
(208, 400, 9800),
(208, 403, 24000),
(210, 404, 87940);

二、聚合函數

（一）計數

COUNT 可用來計算查詢結果中的資料筆數。

以下範例是取得員工數量。

SELECT COUNT(*)
FROM `employee`;

若將 COUNT 中的 * 參數改為欄位名稱，則代表要計算該欄位值不為 null 的資料筆數。

以下範例是計算有銷售紀錄的女員工數量。

SELECT COUNT(`sales`.`total_sales`)
FROM `employee`
LEFT JOIN `sales` ON `sales`.`emp_id` = `employee`.`emp_id`
WHERE `gender` = "F";

以下範例是計算銷售過的不重複客戶數量。

SELECT COUNT(DISTINCT `client_id`)
FROM `sales`;

（二）加總

SUM 可用來計算查詢結果中，指定數值欄位的加總。

以下範例是計算對於客戶「阿狗」的總銷售金額。

SELECT SUM(`total_sales`)
FROM `sales`
JOIN `client` ON `sales`.`client_id` = `client`.`client_id`
WHERE `client_name` = "阿狗";

（三）平均

AVG 可用來計算查詢結果中，指定數值欄位的平均。

以下範例是計算平均每次的銷售金額。

SELECT AVG(`total_sales`)
FROM `sales`;

（四）最大與最小值

MAX 與 MIN 分別可取得查詢結果中，指定數值或日期欄位的最大值或最小值。

以下範例是取得薪資最高的數字。

SELECT MAX(`salary`)
FROM `employee`;

以下範例是取得最年長的員工生日。

SELECT MIN(`birthday`)
FROM `employee`;

三、分組查詢

（一）分組

上一節的聚合函數範例，是將所有的查詢結果當成統計對象，並且只回傳一個值作為結果。而本文的分組查詢，是以欄位值為基礎，將查詢結果進行分組，搭配聚合函數針對各組分別統計。

以下範例是計算各部門的人數。

SELECT `employee`.`dept_id`, `dept_name`, COUNT(*) AS `headcount`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `employee`.`dept_id`;

查詢結果如下。
|| dept_id || dept_name || headcount ||
|-|-|-|
|1|研發|2|
|2|行政|1|
|3|資訊|2|

使用 GROUP BY 語法指定用來分組的欄位。至於要用 SELECT 語法顯示出來的欄位，通常是採用以下幾種：

• 聚合函數結果，此處為「COUNT」。
• 分組欄位，例如部門編號（dept_id）。
• 依賴於分組欄位的其他欄位，例如部門名稱（dept_name）是跟著部門編號這個主鍵。

（二）篩選

若想對分組查詢的結果做條件篩選，需使用 HAVING 語法。以下是延續上述的範例，篩選出人數大於 1 人的部門名稱。

SELECT `dept`.`dept_name`, COUNT(*)  AS `headcount`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `employee`.`dept_id`
HAVING `headcount` > 1;

HAVING 與一般查詢的 WHERE 都是用來進行條件篩選。兩者的差別在於，HAVING 是針對分組後的資料，而WHERE 是針對分組前，因此被排除的資料將不參與分組。

以下的例子是計算各部門中，薪水達 38000 的員工人數。若部門中只有 1 人達到該薪水，則不列出該部門。

SELECT `dept`.`dept_name`, COUNT(*) AS `headcount`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
WHERE `salary` >= 38000
GROUP BY `employee`.`dept_id`
HAVING `headcount` > 1;

查詢結果如下。
|| dept_name || headcount ||
|-|-|
|研發|2|

四、例題

最後提供其他範例，讓讀者更了解分組查詢的用法。

（一）各部門的最低薪資

SELECT `dept_name`, MIN(`salary`) AS `min_salary`
FROM `employee`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `employee`.`dept_id`;

|| dept_name || min_salary ||
|-|-|
|研發|50000|
|行政|29000|
|資訊|35000|

（二）每月生日人數

此處透過一般函數 MONTH 取得月份，並取了別名「month」，根據其值來分組。

SELECT MONTH(`birthday`) AS `month`, COUNT(*) AS `headcount`
FROM `employee`
GROUP BY `month`
ORDER BY `month`;

|| month || headcount ||
|-|-|
|9|2|
|10|2|
|12|1|

（三）每位客戶帶來的總銷售額

SELECT `client_name`, SUM(`sales`.`total_sales`) AS `total_sales`
FROM `client`
JOIN `sales` ON `client`.`client_id` = `sales`.`client_id`
GROUP BY `client`.`client_id`;

|| client_name || total_sales ||
|-|-|
|阿狗|79800|
|阿貓|24000|
|露西|24000|
|艾瑞克|87940|

（四）總銷售額未達 5 萬的部門

SELECT `dept_name`, SUM(`sales`.`total_sales`) AS `total_sales`
FROM `sales`
JOIN `employee` ON `sales`.`emp_id` = `employee`.`emp_id`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
GROUP BY `employee`.`dept_id`
HAVING `total_sales` < 50000;

|| dept_name || total_sales ||
|-|-|
|行政|24000|
|資訊|33800|

（五）各部門對每位客戶的總銷售額

本文的測試資料中，有 3 個部門、5 位客戶。此處會用這兩個欄位來分組，若測試資料夠豐富，最多可達 3 * 5 = 15 種組合。

SELECT `dept_name`, `client_name`, SUM(`sales`.`total_sales`) AS `total_sales`
FROM `sales`
JOIN `employee` ON `sales`.`emp_id` = `employee`.`emp_id`
JOIN `dept` ON `employee`.`dept_id` = `dept`.`dept_id`
JOIN `client` ON `sales`.`client_id` = `client`.`client_id`
GROUP BY `dept`.`dept_id`, `client`.`client_id`
ORDER BY `dept`.`dept_id` ASC;

|| dept_name || client_name || total_sales ||
|-|-|-|
|研發|阿狗|70000|
|研發|艾瑞克|87940|
|行政|阿貓|24000|
|資訊|阿狗|9800|
|資訊|露西|24000|

今日文章到此結束！
最後宣傳一下自己的部落格，我是「新手工程師的程式教室」的作者，主要發表後端相關文章，請多指教