SQLServerで集計する際に使う主なSQL関数です。
- 合計を取得したい。
- 平均値を取得したい。
- 件数を取得したい。
- 特定の項目の最小値を取得したい。
- 特定の項目の最大値を取得したい。
などなど…
集計関数を活用できれば、データを取得した後にプログラム集計を行わなくても、SQLで要件・仕様に合った集計値を取得することができます。
集計関数は、値の集まりに対して計算を実行し、1つの値を返します。
COUNT関数以外の集計関数ではNull値は無視されます。
集計関数は、GROUP BY句を用いてデータをグループ化するSELECTステートメントでよく使用されます。
GROUP BY句を指定した場合は、指定された列ごとにグループ化されたレコードに対する結果の値が集計されます。GROUP BY句を指定しない場合は、抽出対象となるすべてのレコードに対する結果の値が集計されます。
SQLServerの集計に使う主な関数
| 関数名 | 概要 |
|---|---|
| AVG | グループ内の値の平均を返す。（Null値は含まない。） |
| CHECKSUM_AGG | グループ内の値のチェックサムを返す。 |
| COUNT | グループ内の項目数を返す。（int型） |
| COUNT_BIG | グループ内の項目数を返す。（bigint型） |
| GROUPING | GROUP BYリスト内の指定した列の式が集計されるかどうかを示す値を返す。（ROLLUP、CUBE、またはGROUPING SETSによって返されるNull値を区別する。） |
| GROUPING_ID | グループ化のレベルを計算する。 |
| MAX | グループ内の値の最大を返す。 |
| MIN | グループ内の値の最小を返す。 |
| STDEV | 統計的標準偏差を返す。 |
| STDEVP | 母集団に対する標準偏差を返す。 |
| SUM | グループ内の値の合計を返す。 |
| VAR | 統計的変位（分散）を返す。 |
| VARP | 母集団に対する分散を返す。 |
集計に使用するテーブル
本記事では以下のテーブルを使用します。
- GRADE
- ORDER_ITEM
- SALES
- CERTIFICATION_TEST
GRADE
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
CREATE TABLE GRADE ( STUDENT_ID int NOT NULL, SUBJECT_ID int NOT NULL, SCORE tinyint NOT NULL, PRIMARY KEY(STUDENT_ID, SUBJECT_ID) ); INSERT INTO GRADE (STUDENT_ID, SUBJECT_ID, SCORE) VALUES (2001, 1000, 85), (2001, 2000, 73), (2001, 3000, 69), (2002, 1000, 58), (2002, 2000, 62), (2002, 3000, 48), (2003, 1000, 84), (2003, 2000, 94), (2003, 3000, 77), (2004, 1000, 65), (2004, 2000, 58), (2004, 3000, 96), (2005, 1000, 100), (2005, 2000, 86), (2005, 3000, 88); |
ORDER_ITEM
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
CREATE TABLE ORDER_ITEM ( ORDER_ID int NOT NULL PRIMARY KEY, ITEM nvarchar(20) NOT NULL, PRICE int NOT NULL ); INSERT INTO ORDER_ITEM (ORDER_ID, ITEM, PRICE) VALUES (1, 'ITEM1', 1234500), (2, 'ITEM2', 2542600), (3, 'ITEM1', 3516400), (4, 'ITEM3', 8486450), (5, 'ITEM4', 4685000), (6, 'ITEM2', 7809600), (7, 'ITEM4', 5687100); |
SALES
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE SALES ( SALES_ID int NOT NULL PRIMARY KEY, TERRITORY_ID int NULL, SALES_AMOUNT decimal(15, 3) NOT NULL ); INSERT INTO SALES (SALES_ID, TERRITORY_ID, SALES_AMOUNT) VALUES (1, null, 3123454651.784), (2, 100, 2542696812.561), (3, 300, 7351649845.856), (4, 200, 8465185864.125), (5, 400, 4685188980.201), (6, null, 18096843635.158), (7, 200, 158931268635.535), (8, 100, 3812097835.863), (9, 400, 5681382468.271); |
CERTIFICATION_TEST
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 |
CREATE TABLE CERTIFICATION_TEST ( STUDENT_ID int NOT NULL PRIMARY KEY, GENDER_ID tinyint NOT NULL, GRADE_NO tinyint NOT NULL, CLASS_CODE nvarchar(1) NOT NULL, POINT smallint NOT NULL ); INSERT INTO CERTIFICATION_TEST (STUDENT_ID, GENDER_ID, GRADE_NO, CLASS_CODE, POINT) VALUES (19605, 1, 1, 'A', 240), (19772, 2, 2, 'C', 264), (14568, 2, 3, 'B', 208), (18098, 1, 2, 'A', 372), (12956, 1, 3, 'C', 320), (18816, 2, 1, 'B', 262), (13692, 2, 3, 'A', 220), (11119, 1, 3, 'C', 250), (18190, 2, 2, 'A', 204), (17352, 1, 1, 'C', 256), (18783, 1, 3, 'B', 302), (18413, 2, 1, 'A', 256), (13448, 1, 2, 'C', 230), (16241, 2, 2, 'B', 324), (13900, 1, 3, 'A', 344), (10653, 1, 2, 'C', 368), (11244, 2, 2, 'C', 312), (19149, 2, 1, 'B', 236), (16270, 1, 2, 'A', 208), (16078, 1, 2, 'C', 320), (12118, 2, 1, 'B', 252), (16150, 1, 1, 'A', 274), (11314, 1, 3, 'C', 296), (14859, 2, 2, 'A', 390), (18724, 1, 2, 'C', 268), (19458, 2, 3, 'B', 400), (18983, 2, 1, 'A', 238), (18572, 2, 2, 'C', 224), (12409, 1, 3, 'B', 212), (12599, 1, 1, 'A', 314), (13284, 2, 3, 'B', 204), (13882, 1, 2, 'A', 342), (17693, 1, 3, 'C', 374), (13028, 2, 1, 'B', 268), (16715, 2, 3, 'A', 386), (19257, 1, 1, 'C', 230), (14132, 1, 3, 'C', 326), (18936, 1, 3, 'B', 332), (11994, 2, 2, 'A', 282), (15600, 2, 1, 'A', 376), (10077, 1, 2, 'C', 352), (14737, 2, 2, 'B', 204); |
上記のテーブルへのデータの挿入（INSERT）には、1回の実行で複数のレコードを挿入する形式のSQLで記述しています。
1回のINSERTで複数行のレコードをテーブルに一括で挿入する方法については、以下の記事を参照してください。
1回のINSERT（インサートSQL）で複数行のレコードを一括挿入（追加）する
SQLServerやMySQLなどのデータベースで、テーブルにレコードをINSERT文使用して追加するには、通常は以下のように記述します。
...
johobase.com
基本的な集計値を計算する関数
平均を求める AVG
グループまたは全体の平均を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, AVG(SCORE) AS AVERAGE FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | AVERAGE |
|---|---|
| 1000 | 78 |
| 2000 | 74 |
| 3000 | 75 |
件数を求める COUNT
グループまたは全体の件数を求める時に使います。
COUNT関数はint型のデータを返します。
|
1 2 3 4 5 6 7 8 9 |
SELECT ITEM, COUNT(ITEM) ITEMS FROM ORDER_ITEM GROUP BY ITEM ORDER BY ITEM; |
実行結果
| ITEM | ITEMS |
|---|---|
| ITEM1 | 2 |
| ITEM2 | 2 |
| ITEM3 | 1 |
| ITEM4 | 2 |
件数を求める COUNT_BIG
intでは表現できない件数を求める時に使います。
COUNT関数はbigint型のデータを返します。
|
1 2 3 4 5 6 7 8 9 |
SELECT ITEM, COUNT_BIG(ITEM) ITEMS FROM ORDER_ITEM GROUP BY ITEM ORDER BY ITEM; |
実行結果
| ITEM | ITEMS |
|---|---|
| ITEM1 | 2 |
| ITEM2 | 2 |
| ITEM3 | 1 |
| ITEM4 | 2 |
最大を求める MAX
グループまたは全体の最大を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, MAX(SCORE) AS MAXIMUM FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | MAXIMUM |
|---|---|
| 1000 | 100 |
| 2000 | 94 |
| 3000 | 96 |
最小を求める MIN
グループまたは全体の最小を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, MIN(SCORE) AS MINIMUM FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | MINIMUM |
|---|---|
| 1000 | 58 |
| 2000 | 58 |
| 3000 | 48 |
合計を求める SUM
グループまたは全体の合計を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT TERRITORY_ID, SUM(SALES_AMOUNT) AS TOTAL FROM SALES GROUP BY TERRITORY_ID ORDER BY TERRITORY_ID; |
実行結果
| TERRITORY_ID | TOTAL |
|---|---|
| NULL | 21220298286.942 |
| 100 | 6354794648.424 |
| 200 | 167396454499.660 |
| 300 | 7351649845.856 |
| 400 | 10366571448.472 |
チェックサムを取得する関数
チェックサムを求める CHECKSUM_AGG
チェックサムを求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT ITEM, CHECKSUM_AGG(PRICE) AS PRICE_CHECKSUM FROM ORDER_ITEM GROUP BY ITEM ORDER BY ITEM; |
実行結果
| ITEM | PRICE_CHECKSUM |
|---|---|
| ITEM1 | 2585012 |
| ITEM2 | 5367368 |
| ITEM3 | 8486450 |
| ITEM4 | 1162228 |
グルーピングに関する関数
グルーピングの有無を返す GROUPING
列が集計されるかどうかを取得したい時に使います。
GROUPING関数は、GROUP BY句で指定した列が集計されるかどうかを返します。集計される場合は1を返し、集計されない場合は0を返します。
GROUPING関数は、ROLLUP、CUBE、またはGROUPING SETSによって返されるNull値を標準のNull値と区別するために使用されます。
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT TERRITORY_ID, SUM(SALES_AMOUNT) AS TOTAL_AMOUNT, GROUPING(TERRITORY_ID) AS IS_GROUPING FROM SALES GROUP BY TERRITORY_ID WITH ROLLUP ORDER BY IS_GROUPING, TERRITORY_ID; |
実行結果
| TERRITORY_ID | TOTAL_AMOUNT | IS_GROUPING |
|---|---|---|
| NULL | 21220298286.942 | 0 |
| 100 | 6354794648.424 | 0 |
| 200 | 167396454499.660 | 0 |
| 300 | 7351649845.856 | 0 |
| 400 | 10366571448.472 | 0 |
| NULL | 212689768729.354 | 1 |
グループ化のレベルを返す GROUPING_ID
グループ化のレベルを求める時に使います。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
SELECT GRADE_NO, CLASS_CODE, GENDER_ID, COUNT(*) AS STUDENTS, AVG(POINT) AS AVERAGE, GROUPING_ID(GRADE_NO, CLASS_CODE, GENDER_ID) AS GRP_ID FROM CERTIFICATION_TEST GROUP BY GRADE_NO, CLASS_CODE, GENDER_ID WITH ROLLUP; |
実行結果
| GRADE_NO | CLASS_CODE | GENDER_ID | STUDENTS | AVERAGE | GRP_ID |
|---|---|---|---|---|---|
| 1 | A | 1 | 3 | 276 | 0 |
| 1 | A | 2 | 3 | 290 | 0 |
| 1 | A | NULL | 6 | 283 | 1 |
| 1 | B | 2 | 4 | 254 | 0 |
| 1 | B | NULL | 4 | 254 | 1 |
| 1 | C | 1 | 2 | 243 | 0 |
| 1 | C | NULL | 2 | 243 | 1 |
| 1 | NULL | NULL | 12 | 266 | 3 |
| 2 | A | 1 | 3 | 307 | 0 |
| 2 | A | 2 | 3 | 292 | 0 |
| 2 | A | NULL | 6 | 299 | 1 |
| 2 | B | 2 | 2 | 264 | 0 |
| 2 | B | NULL | 2 | 264 | 1 |
| 2 | C | 1 | 5 | 307 | 0 |
| 2 | C | 2 | 3 | 266 | 0 |
| 2 | C | NULL | 8 | 292 | 1 |
| 2 | NULL | NULL | 16 | 291 | 3 |
| 3 | A | 1 | 1 | 344 | 0 |
| 3 | A | 2 | 2 | 303 | 0 |
| 3 | A | NULL | 3 | 316 | 1 |
| 3 | B | 1 | 3 | 282 | 0 |
| 3 | B | 2 | 3 | 270 | 0 |
| 3 | B | NULL | 6 | 276 | 1 |
| 3 | C | 1 | 5 | 313 | 0 |
| 3 | C | NULL | 5 | 313 | 1 |
| 3 | NULL | NULL | 14 | 298 | 3 |
| NULL | NULL | NULL | 42 | 286 | 7 |
|
1 2 3 4 5 6 7 8 9 10 11 |
SELECT GRADE_NO, GENDER_ID, COUNT(*) AS STUDENTS, AVG(POINT) AS AVERAGE, GROUPING_ID(GRADE_NO, GENDER_ID) AS GRP_ID FROM CERTIFICATION_TEST GROUP BY GRADE_NO, GENDER_ID WITH CUBE; |
実行結果
| GRADE_NO | GENDER_ID | STUDENTS | AVERAGE | GRP_ID |
|---|---|---|---|---|
| 1 | 1 | 5 | 262 | 0 |
| 2 | 1 | 8 | 307 | 0 |
| 3 | 1 | 9 | 306 | 0 |
| NULL | 1 | 22 | 296 | 2 |
| 1 | 2 | 7 | 269 | 0 |
| 2 | 2 | 8 | 275 | 0 |
| 3 | 2 | 5 | 283 | 0 |
| NULL | 2 | 20 | 275 | 2 |
| NULL | NULL | 42 | 286 | 3 |
| 1 | NULL | 12 | 266 | 1 |
| 2 | NULL | 16 | 291 | 1 |
| 3 | NULL | 14 | 298 | 1 |
標準偏差を取得する関数
母集団の標本の標準偏差を求める VAR
指定するデータが全体の内の標本（母集団から抜き出した一部分 = サンプル）である場合の標準偏差（統計的標準偏差）を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, STDEV(SCORE) AS STANDARD_DEVIATION FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | STANDARD_DEVIATION |
|---|---|
| 1000 | 16.86119805945 |
| 2000 | 15.3557806704837 |
| 3000 | 18.5553226864962 |
母集団の標準偏差を求める STDEVP
指定するデータが全体のすべて（母集団）である場合の標準偏差を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, STDEVP(SCORE) AS STANDARD_DEVIATION FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | STANDARD_DEVIATION |
|---|---|
| 1000 | 15.0811140172071 |
| 2000 | 13.7346277707115 |
| 3000 | 16.5963851485798 |
分散を取得する関数
母集団の標本の分散（推定値）を求める VAR
指定するデータが全体の内の標本（母集団から抜き出した一部分 = サンプル）である場合の分散（統計的分散）を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, VAR(SCORE) AS VARIANCE FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | VARIANCE |
|---|---|
| 1000 | 284.3 |
| 2000 | 235.8 |
| 3000 | 344.3 |
母集団に対する分散を求める VARP
指定するデータが全体のすべて（母集団）である場合の分散を求める時に使います。
|
1 2 3 4 5 6 7 8 9 |
SELECT SUBJECT_ID, VARP(SCORE) AS VARIANCE FROM GRADE GROUP BY SUBJECT_ID ORDER BY SUBJECT_ID; |
実行結果
| SUBJECT_ID | VARIANCE |
|---|---|
| 1000 | 227.44 |
| 2000 | 188.64 |
| 3000 | 275.44 |
参考URL
集計関数 (Transact-SQL)
SQL Server の他の関数一覧については、以下の記事を参照してください。
SQL Server 日付と時刻の関数一覧
SQL Server で日付や時刻を操作する際に使う、主な SQL 関数です。
実行時の日付が取得したい。
特定の日時か...
johobase.com
SQLServer 文字列関数一覧
SQLServerで文字列を操作する際に使用する主なSQL関数です。
文字列から一部の文字列を抜き出したい。
文字列を置...
johobase.com
