計數與總計

SQL Server 中級

Ginger Grant

Instructor

用 COUNT 檢視總數

SELECT COUNT(*) FROM Incidents

+-----------------+
|(No column name) |
+-----------------+
|6452             | 
+-----------------+

COUNT 搭配 DISTINCT

COUNT(DISTINCT COLUMN_NAME)

T-SQL 中的 COUNT 與 DISTINCT（I）

SELECT COUNT(DISTINCT Country) AS Countries
FROM Incidents

+----------------+
|Countries       |
+-----------------+
|3               |
+-----------------+

T-SQL 中的 COUNT 與 DISTINCT（II）

SELECT COUNT(DISTINCT Country) AS Countries,
COUNT(DISTINCT City) AS Cities 
FROM Incidents

+----------------+-------------+
|Countries       | Cities      |
+-----------------+-------------+
|3               | 3566        |
+-----------------+-------------+

COUNT 聚合

GROUP BY 可與 COUNT() 一起使用，就像 AVG()、MIN()、MAX() 等聚合函式。
使用 ORDER BY 來排序數值：
- ASC 先回傳最小值（預設）
- DESC 先回傳最大值

T-SQL 中的 COUNT 與 GROUP BY

-- 依 Country 小計筆數
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country

+----------------------+-----------------+
|TotalRowsbyCountry   | Country         |
+----------------------+-----------------+
|5452                 |us               |
|750                  |NULL             |
|249                  |ca               |
|1                    |gb               |
+----------------------+-----------------+

T-SQL 中的 COUNT、GROUP BY 與 ORDER BY（I）

-- 依 Country 小計筆數
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country 
ORDER BY Country ASC

+----------------------+-----------------+
|TotalRowsbyCountry   | Country         |
+----------------------+-----------------+
|750                  |NULL             |
|249                  |ca               |
|1                    |gb               |
|5452                 |us               |
+----------------------+-----------------+

T-SQL 中的 COUNT、GROUP BY 與 ORDER BY（II）

-- 依 Country 小計筆數
SELECT COUNT(*) AS TotalRowsbyCountry, Country
FROM Incidents
GROUP BY Country 
ORDER BY Country DESC

+----------------------+-----------------+
|TotalRowsbyCountry   | Country         |
+----------------------+-----------------+
|5452                 |us               |
|1                    |gb               |
|249                  |ca               |
|750                  |NULL             |
+----------------------+-----------------+

用 SUM 做欄位總和

SUM() 可對欄位數值求總和。
用法與其他聚合相同。
結合 GROUP BY，可依指定欄位取得小計。

在 T-SQL 中加總欄位值

-- 依 Country 小計數值
SELECT SUM(DurationSeconds) AS TotalDuration, Country
FROM Incidents
GROUP BY Country

+----------+--------------------+
|Country   |TotalDuration       |
+----------+--------------------+
|us        |17024946.750001565  |
|null      |18859192.800000012  |
|ca        |200975              |
|gb        |120                 |
+----------+--------------------+

一起來練習吧！

SQL Server 中級