# SQL Count()函式

#### 1. SQL COUNT函式簡介

SQL `COUNT`函式是一個聚合函式，它返回符合條件行數。 可以使用SELECT語句中的`COUNT`函式來獲取員工數量，每個部門的員工數量，指定工作崗位的員工數量等。

``````COUNTC ([ALL | DISTINCT] expression);
``````

`COUNT`函式的結果取決於傳遞給它的引數。

• 預設情況下，`COUNT`函式使用`ALL`關鍵字，無論是否指定它。 `ALL`關鍵字表示考慮組中的所有專案，包括重複值。 例如，如果有一個資料集合`(1,2,3,3,4,4)`並應用`COUNT`函式，則結果為`6`

• 如果明確指定`DISTINCT`關鍵字，則僅考慮唯一的非`NULL`值。 如果將`COUNT`函式應用於資料集`(1,2,3,3,4,4)`，則`COUNT`函式返回`4`

``````COUNT(*)
``````

`COUNT(*)`函式返回表中的行數，包括包含`NULL`值的行。

#### 2. SQL COUNT函式範例

2.1. SQL COUNT(*)範例

``````SELECT
COUNT(*)
FROM
employees;
``````

``````+----------+
| COUNT(*) |
+----------+
|       40 |
+----------+
1 row in set
``````

``````SELECT
COUNT(*)
FROM
employees
WHERE
department_id = 6;
``````

``````+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set
``````

``````SELECT
COUNT(*)
FROM
employees
WHERE
job_id = 9;
``````

``````+----------+
| COUNT(*) |
+----------+
|        5 |
+----------+
1 row in set
``````

2.2. SQL COUNT與GROUP BY子句範例

``````SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id;
``````

2.3. SQL COUNT(*)帶有ORDER BY子句的範例

``````SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
ORDER BY
COUNT(*) DESC;
``````

2.4. SQL COUNT帶有HAVING子句的範例

``````SELECT
e.department_id,
department_name,
COUNT(*)
FROM
employees e
INNER JOIN departments d ON d.department_id = e.department_id
GROUP BY
e.department_id
HAVING
COUNT(*) > 5
ORDER BY
COUNT(*) DESC;
``````

2.5. SQL COUNT(DISTINCT表示式)範例

``````SELECT
COUNT(job_id)
FROM
employees;
``````

``````+---------------+
| COUNT(job_id) |
+---------------+
|            40 |
+---------------+
1 row in set
``````

``````SELECT
COUNT(DISTINCT job_id)
FROM
employees;
+------------------------+
| COUNT(DISTINCT job_id) |
+------------------------+
|                     19 |
+------------------------+
1 row in set
``````

``````SELECT
COUNT(DISTINCT manager_id)
FROM
employees;
+----------------------------+
| COUNT(DISTINCT manager_id) |
+----------------------------+
|                         10 |
+----------------------------+
1 row in set
``````