MySQL内部関数カウント・合計・平均・最大値・最小値(GROUP BY節で使う関数)
- eの平均値を求める〔AVG(e)〕
- eの全ビットに対するビット毎のANDを返す〔BIT_AND(e)〕
- eの全ビットに対するビット毎のORを返す〔BIR_OR(e)〕
- eの全ビットに対するビットごとのXORを返す ※MySQL4.1.1~〔BIT_XOR(e)〕
- レコードのカウント〔COUNT(e)〕
- NULL以外の異なる各値の数のカウントを返す〔COUNT(DISTINCT e[, e ... ])〕
- グループ内の値を連結 ※MySQL4.1~〔GROUP_CONCAT(e)〕
- カラムeの最小値を返す〔MIN(e)〕
- カラムeの最大値を返す〔MAX(e)〕
- カラムeの合計を返す〔SUM(e)〕
- eの標準偏差を返す ※MySQL4.1~〔VARIANCE(e)〕
- STD(e), STDDEV(e)〔STD(e), STDDEV(e)〕
- HAVING〔HAVING〕
- このページで使用しているサンプルデータ
eの平均値を求める
AVG(e)
unknown
# 科目毎にテストの平均値を求める mysql> SELECT Subject, AVG(TestScore) Enter -> FROM tbl_Test Enter -> GROUP BY Subject; Enter +---------+----------------+ | Subject | AVG(TestScore) | +---------+----------------+ | 国語 | 68.6667 | | 算数 | 76.6667 | +---------+----------------+ 2 rows in set (0.00 sec)
eの全ビットに対するビット毎のANDを返す
BIT_AND(e)
unknown
カラムeの全ビットに対するビット毎のANDを返します。※64ビット(BIGINT)の精度。
一致するレコードがない場合、MySQL4.0.17以降は18446744073709551615、それ以前のバージョンは-1を返します。
eの全ビットに対するビット毎のORを返す
BIR_OR(e)
unknown
カラムeの全ビットに対するビット毎のORを返します。※64ビット(BIGINT)の精度
一致するレコードがない場合に、0を返します。
eの全ビットに対するビットごとのXORを返す ※MySQL4.1.1~
BIT_XOR(e)
unknown
カラムeの全ビットに対するビット毎のXORを返します。※64ビット(BIGINT)の精度
一致するレコードがない場合に、0を返します。
レコードのカウント
COUNT(e)
unknown
SELECT文で抽出したレコードからNULL以外の値の数をカウントします。
# テーブル(tbl_Test)でカラム(Subject)が「算数」のレコードをカウント mysql> SELECT COUNT(*) FROM tbl_Test Enter -> WHERE Subject='算数'; Enter +----------+ | COUNT(*) | +----------+ | 6 | +----------+ 1 row in set (0.01 sec) # テーブル(tbl_Student)でカラム(ClassName)が「A」のレコードをカウント mysql> SELECT COUNT(*) FROM tbl_Student Enter -> WHERE ClassName='A'; Enter +----------+ | COUNT(*) | +----------+ | 4 | +----------+ 1 row in set (0.00 sec) # 2つのテーブルからStudentIDが同じ生徒をカウントし、名前でソートして表示 mysql> SELECT concat(s.LastName,' ',s.FirstName) as FullName, COUNT(*) Enter -> FROM tbl_Student as s, tbl_Test as t Enter -> WHERE s.StudentID=t.StudentID Enter -> GROUP BY FullName; Enter +-------------+----------+ | FullName | COUNT(*) | +-------------+----------+ | 井上 太郎 | 2 | | 新井 ひろみ | 2 | | 青木 一郎 | 2 | | 青木 尚 | 2 | +-------------+----------+ 4 rows in set (0.00 sec)
NULL以外の異なる各値の数のカウントを返す
COUNT(DISTINCT e[, e ... ])
unknown
NULL以外を重複なしにそれぞれの値をカウントします。
# テーブル(tbl_Test)から重複なしにカラム(TestSocre)の値をカウント mysql> SELECT COUNT(DISTINCT TestScore) FROM tbl_Test; Enter +---------------------------+ | COUNT(DISTINCT TestScore) | +---------------------------+ | 8 | +---------------------------+ 1 row in set (0.00 sec)
グループ内の値を連結 ※MySQL4.1~
GROUP_CONCAT(e)
unknown
[ORDER BY {unsigned_integer | col_name | formula} [ASC | DESC] [,col ...]]
[SEPARATOR str_val])
グループ内の値を連結した文字列を返します。
mysql> SELECT concat(s.LastName,' ',s.FirstName) as FullName, Enter -> GROUP_CONCAT(t.TestScore) Enter -> FROM tbl_Test as t, tbl_Student as s Enter -> GROUP BY FullName; Enter +-------------+-------------------------------------+ | FullName | GROUP_CONCAT(t.TestScore) | +-------------+-------------------------------------+ | 井上 太郎 | 80,80,46,50,90,60,98,30,78,60,80,90 | | 宇野 加奈子 | 30,78,60,80,90,80,80,46,50,90,60,98 | | 新井 ひろみ | 60,98,30,78,60,80,90,80,80,46,50,90 | | 青木 一郎 | 80,80,46,50,90,60,98,30,78,60,80,90 | | 青木 尚 | 98,30,78,60,80,90,80,80,46,50,90,60 | | 石田 ゆりえ | 98,30,78,60,80,90,80,80,46,50,90,60 | +-------------+-------------------------------------+ 6 rows in set (0.17 sec)
カラムeの最小値を返す
MIN(e)
unknown
指定したカラムeの最小値を返します。
文字列引数をとる場合、最小の文字列値を返します。
# テーブル(tbl_Test)から科目毎に一番低い点数を表示 mysql> SELECT Subject, MIN(TestScore) FROM tbl_Test Enter -> GROUP BY Subject; Enter +---------+----------------+ | Subject | MIN(TestScore) | +---------+----------------+ | 国語 | 30 | | 算数 | 50 | +---------+----------------+ 2 rows in set (0.00 sec)
カラムeの最大値を返す
MAX(e)
unknown
指定したカラムeの最大値を返します。
文字列引数をとる場合、最大の文字列値を返します。
# テーブル(tbl_Test)から科目毎に一番高い点数を表示 mysql> SELECT Subject,MAX(TestScore) FROM tbl_Test Enter -> GROUP BY Subject; Enter +---------+----------------+ | Subject | MAX(TestScore) | +---------+----------------+ | 国語 | 98 | | 算数 | 90 | +---------+----------------+ 2 rows in set (0.00 sec)
カラムeの合計を返す
SUM(e)
unknown
指定したカラムeの合計値を返します。
返り値の結果セットにレコードが含まれていない場合はNULLを返します。
# 生徒(StudentID)毎に全科目の総合取得点数を表示 mysql> SELECT StudentID, SUM(TestScore) FROM tbl_Test Enter -> GROUP BY StudentID; Enter +-----------+----------------+ | StudentID | SUM(TestScore) | +-----------+----------------+ | A01 | 158 | | A02 | 110 | | A03 | 168 | | B01 | 130 | | B02 | 170 | | B03 | 106 | +-----------+----------------+ 6 rows in set (0.01 sec)
eの標準偏差を返す ※MySQL4.1~
VARIANCE(e)
unknown
カラムeの標準偏差を返します。
# 科目毎の標準偏差を求める mysql> SELECT Subject, VARIANCE(TestScore) as '標準偏差' FROM tbl_Test Enter -> GROUP BY Subject; Enter +---------+----------+ | Subject | 標準偏差 | +---------+----------+ | 国語 | 535.5556 | | 算数 | 247.2222 | +---------+----------+ 2 rows in set (0.09 sec)
STD(e), STDDEV(e)
STD(e), STDDEV(e)
unknown
カラムeの標準偏差(VARIANCE()の平方根)を返します(SQL-99 に対する拡張)。
STDDEV()はOracleとの互換性を確保するために提供されています。
mysql> SELECT Subject, STD(TestScore) Enter -> FROM tbl_Test Enter -> GROUP BY Subject; Enter +---------+----------------+ | Subject | Subject(TestScore) | +---------+----------------+ | 国語 | 23.1421 | | 算数 | 15.7233 | +---------+----------------+ 2 rows in set (0.07 sec)
HAVING
HAVING
unknown
HAVING句は、レコードの抽出結果をグループ化した後、グループ化した抽出結果に対して条件を指定する場合に使用します。
グループ化の前に抽出条件を指定する場合は、WHERE句を使用します。
# 数学・国語の総合点数が160点以上の生徒を抽出 mysql> SELECT StudentID, SUM(TestScore) Enter -> FROM tbl_Test Enter -> GROUP BY StudentID Enter -> HAVING SUM(TestScore) >= 160; Enter +-----------+----------------+ | StudentID | SUM(TestScore) | +-----------+----------------+ | A03 | 168 | | B02 | 170 | +-----------+----------------+ 2 rows in set (0.00 sec)
このページで使用しているサンプルデータ
unknown
# データベース(db_Student)作成 CREATE DATABASE IF NOT EXISTS db_Student; #データベース選択 USE db_Student; # テーブル(tbl_Test)作成 CREATE TABLE tbl_Test( SEQ INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, StudentID VARCHAR(3) DEFAULT NULL, Subject VARCHAR(20) DEFAULT NULL, TestScore INTEGER DEFAULT NULL ); # データ挿入 INSERT INTO tbl_Test (StudentID,Subject,TestScore) VALUES ('A01','国語',98), ('A02','国語',30), ('A03','国語',78), ('A01','算数',60), ('A02','算数',80), ('A03','算数',90), ('B01','国語',80), ('B02','国語',80), ('B03','国語',46), ('B01','算数',50), ('B02','算数',90), ('B03','算数',60); # テーブル(tbl_Student)作成 CREATE TABLE tbl_Student( StudentID VARCHAR(3) NOT NULL PRIMARY KEY, ClassName CHAR(2) NOT NULL, FirstName VARCHAR(20) DEFAULT NULL, LastName VARCHAR(20) DEFAULT NULL, Sex CHAR(2) DEFAULT NULL ); # データ挿入 INSERT INTO tbl_Student (StudentID,ClassName,FirstName,LastName,Sex) VALUES ('A01','A','尚','青木','f'), ('A02','A','太郎','井上','m'), ('A03','A','加奈子','宇野','m'), ('B01','B','ひろみ','新井','f'), ('B02','B','一郎','青木','m'), ('B03','A','ゆりえ','石田','f'); ------------------------------------------------------------ # テーブル(tbl_Test)の中身 mysql> SELECT * FROM tbl_Test; Enter +-----+-----------+---------+-----------+ | SEQ | StudentID | Subject | TestScore | +-----+-----------+---------+-----------+ | 1 | A01 | 国語 | 98 | | 2 | A02 | 国語 | 30 | | 3 | A03 | 国語 | 78 | | 4 | A01 | 算数 | 60 | | 5 | A02 | 算数 | 80 | | 6 | A03 | 算数 | 90 | | 7 | B01 | 国語 | 80 | | 8 | B02 | 国語 | 80 | | 9 | B03 | 国語 | 46 | | 10 | B01 | 算数 | 50 | | 11 | B02 | 算数 | 90 | | 12 | B03 | 算数 | 60 | +-----+-----------+---------+-----------+ 12 rows in set (0.00 sec) # テーブル(tbl_Student)の中身 mysql> SELECT * FROM tbl_Student; Enter +-----------+-----------+-----------+----------+------+ | StudentID | ClassName | FirstName | LastName | Sex | +-----------+-----------+-----------+----------+------+ | A01 | A | 尚 | 青木 | f | | A02 | A | 太郎 | 井上 | m | | A03 | A | 加奈子 | 宇野 | m | | B01 | B | ひろみ | 新井 | f | | B02 | B | 一郎 | 青木 | m | | B03 | A | ゆりえ | 石田 | f | +-----------+-----------+-----------+----------+------+ 6 rows in set (0.00 sec)4 rows in set (0.01 sec)