T-SQL の集計関数を使う 2 - MAX・MIN・AVG

T-SQL の集計関数を使ってみよう 2

前回は「 T-SQL の集計関数を使う 1 」で必要なテーブルを作って、集計関数の COUNT や SUM を使ってみました。

まだ Student、Test、TestResult テーブルを作っていない人は、前のページに戻って作っておいてください。

ここでは、よく使う SQL Server の組み込み関数の MAX・MIN・AVG の使い方をご紹介します。

MAX は結果セットのなかでの指定したカラムの最大値を返し、 MIN は最小値を返す関数です。

例えば、TestResult テーブルの TestID が 1 の最高得点、最低得点は次のように取得できます。

SELECT  MAX(Score) AS MAX_Score,
        MIN(Score) AS MIN_Score
FROM    TestResult
WHERE   TestID = 1;

[ 実行結果 ]

ちゃんと TestID = 1 の最高得点と最低得点が取得できていますね。

AVG は結果セットのなかで、指定したカラムの平均値を返してくれる関数です。

TestID が 1 の平均点は次のように取得することができます。

SELECT  AVG(Score) AS AVG_Score
FROM    TestResult
WHERE   TestID = 1;

[ 実行結果 ]

(85 + 60 + 98 + 73) / 4 = 79 なので、正しく取得できています。

少し気をつけなければいけないのは、値に NULL が入っているときの動きです。

例えば、TestID が 2 では、StudentID = 2 の点数が NULL になっています。

この状態で AVG(Score) を取得すると、値は 4 で割られるのではなく、 3 で割られ、(77 + 92 + 81) / 3 の計算結果が返ってきます。

SELECT  AVG(Score) AS AVG_Score
FROM    TestResult
WHERE   TestID = 2;

[ 実行結果 ]

もし 0 点扱いしたいケースがあれば、ISNULL で 0 に変換してから AVG 関数を使うと (77 + 0 + 92 + 81) / 4 の値が取得できます。

SELECT  AVG(ISNULL(Score, 0)) AS AVG_Score
FROM    TestResult
WHERE   TestID = 2;

[ 実行結果 ]

先ほどの結果でお気づきになった方もいらっしゃるかもしれませんが、(77 + 92 + 81) / 3 は 83.3333333 なのに、83 になっていますね。

AVG は評価対象のカラムのデータ型によって、戻り値のデータ型が決まります。 Score は INT 型なので、戻り値も INT 型になってしまっています。

小数点以下の値まで取得したい時は CAST 等を使って DECIMAL にキャストしてから、AVG を使うことで取得できます。

SELECT  AVG(CAST(Score AS DECIMAL)) AS AVG_Score
FROM    TestResult
WHERE   TestID = 2;

[ 実行結果 ]

次は、GROUP BY を使ってグルーピングして、集計してみましょう。