首頁 » SQL 教學 » SQL GROUP BY

SQL GROUP BY

摘要：在本教學中，您將學習如何使用 SQL 的 GROUP BY 子句，根據一或多個欄位來分組資料列。

SQL GROUP BY 子句介紹

GROUP BY 是 SELECT 陳述式中一個可選的子句。 GROUP BY 子句允許您根據一或多個欄位的值來分組資料列。它會針對每個群組回傳一個資料列。

以下顯示 GROUP BY 子句的基本語法

SELECT
	column1,
	column2,
	aggregate_function(column3)
FROM
	table_name
GROUP BY
	column1,
	column2;Code language: SQL (Structured Query Language) (sql)

下圖說明 GROUP BY 子句的工作方式

左側的表格有兩個欄位，分別是 id 和 fruit。當您將 GROUP BY 子句套用到 fruit 欄位時，它會回傳一個結果集，其中包含 fruit 欄位的唯一值

SELECT 
    fruit
FROM 
    sample_table
GROUP BY 
    fruit;

在實務上，您通常會將 GROUP BY 子句與彙總函數一起使用，例如 MIN、 MAX、 AVG、 SUM 或 COUNT，以計算提供每個群組資訊的度量。

例如，以下說明 GROUP BY 子句如何與 COUNT 彙總函數一起運作

在此範例中，我們根據 fruit 欄位的值來分組資料列，並將 COUNT 函數套用到 id 欄位。結果集包含 fruit 欄位的唯一值，以及對應的資料列數。

SELECT 
   fruit, COUNT(id) 
FROM
   sample_table
GROUP BY 
   fruit;

出現在 GROUP BY 子句中的欄位稱為分組欄位。如果分組欄位包含 NULL 值，所有 NULL 值都會被彙總到一個群組中，因為 GROUP BY 子句會將所有 NULL 值視為相等。

SQL GROUP BY 範例

我們將使用範例資料庫中的 employees 和 departments 表格，來示範 GROUP BY 子句的工作方式。

emp_dept_tables

以下範例使用 GROUP BY 子句來分組 employees 表格中 department_id 欄位的值

SELECT 
    department_id
FROM 
    employees
GROUP BY 
    department_id;Code language: SQL (Structured Query Language) (sql)

輸出

試試看

+---------------+
| department_id |
+---------------+
|             1 |
|             2 |
|             3 |
|             4 |
|             5 |
|             6 |
|             7 |
|             8 |
|             9 |
|            10 |
|            11 |
+---------------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

在這個範例中

  • 首先，SELECT 子句會回傳 employees 表格中 department_id 欄位的所有值。
  • 其次，GROUP BY 子句會將所有值分組。

employees 表格的 department_id 欄位有 40 列，包括重複的 department_id 值。但是，GROUP BY 會將這些值分組成不同的群組。

如果沒有彙總函數，GROUP BY 的行為類似於 DISTINCT 關鍵字

SELECT 
   DISTINCT department_id
FROM 
   employees
ORDER BY 
   department_id;Code language: SQL (Structured Query Language) (sql)

試試看

當您將 GROUP BY 子句與彙總函數一起使用時，它會更有用。

例如，以下陳述式將 GROUP BY 子句與 COUNT 函數一起使用，來計算每個部門的員工數量

SELECT
	department_id,
	COUNT(employee_id) headcount
FROM
	employees
GROUP BY
	department_id;Code language: SQL (Structured Query Language) (sql)

試試看

輸出

+---------------+-----------+
| department_id | headcount |
+---------------+-----------+
|             1 |         1 |
|             2 |         2 |
|             3 |         6 |
|             4 |         1 |
|             5 |         7 |
|             6 |         5 |
|             7 |         1 |
|             8 |         6 |
|             9 |         3 |
|            10 |         6 |
|            11 |         2 |
+---------------+-----------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

運作方式。

  • 首先，GROUP BY 子句會根據部門 ID 將 employees 表格中的資料列分組。
  • 其次，COUNT(employee_id) 會回傳每個群組中員工 ID 值的數量。

SQL GROUP BY 與 INNER JOIN 範例

以下範例回傳每個部門的員工數量。它使用 INNER JOIN 子句將部門名稱包含在結果中

SELECT
	department_name,
	COUNT(employee_id) headcount
FROM
	employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
        department_name;Code language: SQL (Structured Query Language) (sql)

試試看

輸出

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Accounting       |         2 |
| Administration   |         1 |
| Executive        |         3 |
| Finance          |         6 |
| Human Resources  |         1 |
| IT               |         5 |
| Marketing        |         2 |
| Public Relations |         1 |
| Purchasing       |         6 |
| Sales            |         6 |
| Shipping         |         7 |
+------------------+-----------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

SQL GROUP BY 與 ORDER BY 範例

以下範例使用 ORDER BY 子句，依人數對部門進行排序

SELECT 
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY department_name
ORDER BY headcount DESC;Code language: SQL (Structured Query Language) (sql)

試試看

輸出

+------------------+-----------+
| department_name  | headcount |
+------------------+-----------+
| Shipping         |         7 |
| Sales            |         6 |
| Finance          |         6 |
| Purchasing       |         6 |
| IT               |         5 |
| Executive        |         3 |
| Marketing        |         2 |
| Accounting       |         2 |
| Human Resources  |         1 |
| Administration   |         1 |
| Public Relations |         1 |
+------------------+-----------+
11 rows in set (0.00 sec)Code language: JavaScript (javascript)

請注意，您可以在 ORDER BY 子句中使用 headcount 別名或 COUNT(employee_id)。

SQL GROUP BY 與 HAVING 範例

以下範例使用 HAVING 子句來尋找人數大於 5 的部門

SELECT 
    department_name,
    COUNT(employee_id) headcount
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY department_name
HAVING headcount > 5
ORDER BY headcount DESC;Code language: SQL (Structured Query Language) (sql)

試試看

輸出

+-----------------+-----------+
| department_name | headcount |
+-----------------+-----------+
| Shipping        |         7 |
| Sales           |         6 |
| Finance         |         6 |
| Purchasing      |         6 |
+-----------------+-----------+
4 rows in set (0.00 sec)Code language: JavaScript (javascript)

SQL GROUP BY 與 MIN、MAX 和 AVG 範例

以下查詢回傳每個部門員工的最低、最高和平均薪資。

SELECT 
    department_name,
    MIN(salary) min_salary,
    MAX(salary) max_salary,
    ROUND(AVG(salary), 2) average_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;Code language: SQL (Structured Query Language) (sql)

試試看

輸出

+------------------+------------+------------+----------------+
| department_name  | min_salary | max_salary | average_salary |
+------------------+------------+------------+----------------+
| Accounting       |    8300.00 |   12000.00 |       10150.00 |
| Administration   |    4400.00 |    4400.00 |        4400.00 |
| Executive        |   17000.00 |   24000.00 |       19333.33 |
| Finance          |    6900.00 |   12000.00 |        8600.00 |
| Human Resources  |    6500.00 |    6500.00 |        6500.00 |
| IT               |    4200.00 |    9000.00 |        5760.00 |
| Marketing        |    6000.00 |   13000.00 |        9500.00 |
| Public Relations |   10000.00 |   10000.00 |       10000.00 |
| Purchasing       |    2500.00 |   11000.00 |        4150.00 |
| Sales            |    6200.00 |   14000.00 |        9616.67 |
| Shipping         |    2700.00 |    8200.00 |        5885.71 |
+------------------+------------+------------+----------------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

SQL GROUP BY 與 SUM 函數範例

若要取得每個部門的總薪資，您可以將 SUM 函數套用到 salary 欄位，並按 department_id 欄位對員工進行分組，如下所示

SELECT 
    department_name,
    SUM(salary) total_salary
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
GROUP BY 
    department_name;Code language: SQL (Structured Query Language) (sql)

試試看

輸出

+------------------+--------------+
| department_name  | total_salary |
+------------------+--------------+
| Accounting       |     20300.00 |
| Administration   |      4400.00 |
| Executive        |     58000.00 |
| Finance          |     51600.00 |
| Human Resources  |      6500.00 |
| IT               |     28800.00 |
| Marketing        |     19000.00 |
| Public Relations |     10000.00 |
| Purchasing       |     24900.00 |
| Sales            |     57700.00 |
| Shipping         |     41200.00 |
+------------------+--------------+
11 rows in set (0.01 sec)Code language: JavaScript (javascript)

SQL GROUP BY 多個欄位

到目前為止，您已經看到我們按一個欄位分組所有員工。例如，以下子句會將 department_id 欄位中具有相同值的所有資料列放在一個群組中。

GROUP BY department_idCode language: SQL (Structured Query Language) (sql)

如果按 department_id 和 job_id 欄位中的值分組員工，該怎麼辦？

GROUP BY department_id, job_idCode language: SQL (Structured Query Language) (sql)

此子句會將在 department_id 和 job_id 欄位中都具有相同值的所有員工放在一個群組中。

以下陳述式會將在 department_id 和 job_id 欄位中都具有相同值的資料列分在同一個群組中，然後回傳每個群組的資料列。

SELECT 
    department_name,
    job_title,
    COUNT(employee_id)
FROM
    employees e
        INNER JOIN
    departments d ON d.department_id = e.department_id
        INNER JOIN
    jobs j ON j.job_id = e.job_id
GROUP BY department_name , 
         job_title;Code language: SQL (Structured Query Language) (sql)

試試看

+------------------+---------------------------------+--------------------+
| department_name  | job_title                       | COUNT(employee_id) |
+------------------+---------------------------------+--------------------+
| Accounting       | Accounting Manager              |                  1 |
| Accounting       | Public Accountant               |                  1 |
| Administration   | Administration Assistant        |                  1 |
| Executive        | Administration Vice President   |                  2 |
| Executive        | President                       |                  1 |
| Finance          | Accountant                      |                  5 |
| Finance          | Finance Manager                 |                  1 |
| Human Resources  | Human Resources Representative  |                  1 |
| IT               | Programmer                      |                  5 |
| Marketing        | Marketing Manager               |                  1 |
| Marketing        | Marketing Representative        |                  1 |
| Public Relations | Public Relations Representative |                  1 |
| Purchasing       | Purchasing Clerk                |                  5 |
| Purchasing       | Purchasing Manager              |                  1 |
| Sales            | Sales Manager                   |                  2 |
| Sales            | Sales Representative            |                  4 |
| Shipping         | Shipping Clerk                  |                  2 |
| Shipping         | Stock Clerk                     |                  1 |
| Shipping         | Stock Manager                   |                  4 |
+------------------+---------------------------------+--------------------+
19 rows in set (0.00 sec)Code language: PHP (php)

摘要

  • GROUP BY 子句會根據一或多個欄位的值將資料列分組成不同的群組。
  • 將彙總函數與 GROUP BY 子句一起使用，以計算每個群組的彙總值。
這個教學對您有幫助嗎？
SQL CROSS JOIN
上一個
SQL CROSS JOIN
下一個
SQL HAVING
SQL HAVING