これまでは、表から特定の1行について、必要な列のデータを取り出す方法について説明しました。単一行SELECT文では、複数の行のデータを集計した結果を取り出すことができます。集計により、データの合計、平均、最大、最小、および行数そのものを求めることができます。それには、選択リストに集合関数を指定します。指定例を以下に示します。
発注表から発注数量の合計を取り出します。
SELECT SUM(発注数量) INTO :TOTALQTY FROM 在庫管理.発注表
“SUM”は列の値の合計値を求めるための関数です。ほかに、平均値を求める“AVG”、最大値を求める“MAX”、最小値を求める“MIN”、および行数を求める“COUNT”が使用できます。これらの関数を“集合関数”と呼びます。
例1の単一行SELECT文では、取り出す行を特定するためのWHERE句が指定されていないことに気付かれたと思います。単一行SELECT文で取り出せるのは、1行分のデータだけですから、これまでの例では必ず取り出す行を特定するための条件を指定していました。一方この例では、表中のすべての行に対する集計結果を取り出すわけですから、行を特定するための条件は必要ありません。なお、ある条件を満たす行だけを集計の対象としたい場合には、WHERE句を指定します。この場合、WHERE句の探索条件が真になる行だけが、集計の対象となります。
また、例1では、集合関数は列の値に適用していますが、演算式の結果に適用することもできます。この例を以下に示します。
発注表から、取引先の番号が“72”の取引先について、発注数量の合計と発注価格の合計を求めます。なお、発注価格は、仕入価格×発注数量で求められます。
SELECT SUM(発注数量), SUM(仕入価格 * 発注数量) INTO :TOTALQTY, :TOTALPRICE FROM 在庫管理.発注表 WHERE 取引先 = 72
図2.6 演算式の結果に集合関数を指定した例
集合関数には以下の種類があります。
集合関数は、COUNT(*)関数を除き以下の2通りの指定ができます。ただし、MAX関数およびMIN関数では、どちらを指定しても結果は同じです。
指定した列の値からNULL値を除いた行に、集合関数を適用します。
指定した列の値がNULL値の行および値の重複する行を除いて、集合関数を適用します。
ALL指定をした関数を“ALL集合関数”、DISTINCT指定をした関数を“DISTINCT集合関数”と呼びます。ALLおよびDISTINCTを省略すると、ALL集合関数になります。
以下に、ALL集合関数およびDISTINCT集合関数で適用となる行を示します。ここでは、表TBL1を使用して説明します。
図2.7 ALL集合関数およびDISTINCT集合関数の処理対象行
集合関数の指定方法を以下に示します。
種 類 | 関数名 | 指定方法(注1) | 意 味 |
---|---|---|---|
COUNT(*) | COUNT(*) (注2) | 表の行数 | |
ALL集合関数 | AVG | AVG(ALL 値式) | 値式の平均(重複行あり) |
MAX | MAX(ALL 値式) | 値式の最大(重複行あり) | |
MIN | MIN(ALL 値式) | 値式の最小(重複行あり) | |
SUM | SUM(ALL 値式) | 値式の合計(重複行あり) | |
COUNT | COUNT(ALL 値式) | 値式の行数(重複行あり) | |
DISTINCT集合関数 | AVG | AVG(DISTINCT 値式) | 値式の平均(重複行なし) |
MAX | MAX(DISTINCT 値式) | 値式の最大(重複行なし) | |
MIN | MIN(DISTINCT 値式) | 値式の最小(重複行なし) | |
SUM | SUM(DISTINCT 値式) | 値式の合計(重複行なし) | |
COUNT | COUNT(DISTINCT 値式) | 値式の行数(重複行なし) |
注1) ALL集合関数を指定する場合、キーワード“ALL”は省略できます。
注2) COUNT(*)では列は指定しません。
“表2.3 集合関数の指定方法”のように、ALL集合関数もDISTINCT集合関数も、引数に値式を指定します。引数には、列名を使用した演算式も指定できます。
集合関数の対象とする列のデータ型と、結果のデータ型の関係を以下に示します。
集合関数 | 対象とする列のデータ型 | 結果のデータ型 |
---|---|---|
COUNT | --- | INTEGER |
SUM | INTEGER | INTEGER (注) |
SMALLINT | INTEGER (注) | |
DECIMAL(p,q) | DECIMAL(m,n) | |
NUMERIC(p,q) | ||
REAL | REAL | |
DOUBLE PRECISION | DOUBLE PRECISION | |
INTERVAL | INTERVAL | |
AVG | INTEGER | DECIMAL(12,2) (注) |
SMALLINT | DECIMAL(7,2) (注) | |
DECIMAL(p,q) | DECIMAL(m,n) | |
NUMERIC(p,q) | ||
REAL | REAL | |
DOUBLE PRECISION | DOUBLE PRECISION | |
INTERVAL | INTERVAL | |
MAX/MIN | INTEGER | INTEGER |
SMALLINT | SMALLINT | |
DECIMAL(p,q) | DECIMAL(p,q) | |
NUMERIC(p,q) | NUMERIC(p,q) | |
REAL | REAL | |
DOUBLE PRECISION | DOUBLE PRECISION | |
CHARACTER | CHARACTER | |
CHARACTER VARYING | CHARACTER VARYING | |
NATIONAL CHARACTER | NATIONAL CHARACTER | |
NATIONAL CHARACTER VARYING | NATIONAL CHARACTER VARYING | |
DATE | DATE | |
TIME | TIME | |
TIMESTAMP | TIMESTAMP | |
INTERVAL | INTERVAL |
p: 精度 q: 位取り
注) 中間精度はINTEGERとなります。