カウントと合計

中級 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 は AVG()、MIN()、MAX() などと同様に COUNT() と組み合わせて使用できる
ORDER BY コマンドで値を並べ替えられる
- ASC：小さい値から順に返す（デフォルト）
- DESC：大きい値から順に返す

T-SQLでのCOUNTとGROUP BY

-- Count the rows, subtotaled 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）

-- Count the rows, subtotaled by 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）

-- Count the rows, subtotaled by 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での列値の合計

-- Calculate the values subtotaled by 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