[SQL]データ集計

サンプルテーブル作成

sqlite> CREATE TABLE tbl2 (id INTEGER NOT NULL PRIMARY KEY, date DATE NOT NULL, name TEXT NOT NULL, math INTEGER, physics INTEGER, chemistry INTEGER);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-02-25'), 'Albert', 69, 42, 74);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-02-25'), 'Emily', 80, 93, 85); sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-02-25'), 'John', 35, 42, 38);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-07-20'), 'Albert', 72, 53, 68);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-07-20'), 'Emily', 75, 85, 87);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-07-20'), 'John', 46, 54, 46);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-12-15'), 'Albert', 63, 54, 65);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-12-15'), 'Emily', 85, 88, 92);
sqlite> INSERT INTO tbl2 VALUES (NULL, date('2010-12-15'), 'John', 43, 53, 41);
sqlite> SELECT * FROM tbl2;
id date name math physics chemistry
---------- ---------- ---------- ---------- ---------- ----------
1 2010-02-25 Albert 69 42 74
2 2010-02-25 Emily 80 93 85
3 2010-02-25 John 35 42 38
4 2010-07-20 Albert 72 53 68
5 2010-07-20 Emily 75 85 87
6 2010-07-20 John 46 54 46
7 2010-12-15 Albert 63 54 65
8 2010-12-15 Emily 85 88 92
9 2010-12-15 John 43 53 41

合計
各テストの合計点を出す

sqlite> SELECT *,math+physics+chemistry AS total FROM tbl2;
id date name math physics chemistry total
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2010-02-25 Albert 69 42 74 185
2 2010-02-25 Emily 80 93 85 258
3 2010-02-25 John 35 42 38 115
4 2010-07-20 Albert 72 53 68 193
5 2010-07-20 Emily 75 85 87 247
6 2010-07-20 John 46 54 46 146
7 2010-12-15 Albert 63 54 65 182
8 2010-12-15 Emily 85 88 92 265
9 2010-12-15 John 43 53 41 137

平均
math, physics を足して 2 で割った結果を出す

sqlite> SELECT *, (math+physics)/2.0 FROM tbl2;
id date name math physics chemistry (math+physics)/2.0
---------- ---------- ---------- ---------- ---------- ---------- ------------------
1 2010-02-25 Albert 69 42 74 55.5
2 2010-02-25 Emily 80 93 85 86.5
3 2010-02-25 John 35 42 38 38.5
4 2010-07-20 Albert 72 53 68 62.5
5 2010-07-20 Emily 75 85 87 80.0
6 2010-07-20 John 46 54 46 50.0
7 2010-12-15 Albert 63 54 65 58.5
8 2010-12-15 Emily 85 88 92 86.5
9 2010-12-15 John 43 53 41 48.0

GROUP BY
name が同じものを集めて math の平均値を出す

sqlite> SELECT name,AVG(math) FROM tbl2 GROUP BY name;
name AVG(math)
---------- ----------
Albert 68.0
Emily 80.0
John 41.3333333

ORDER BY
2010-12-15 のテスト結果のみ表示してみる

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15');
date name math
---------- ---------- ----------
2010-12-15 Albert 63
2010-12-15 Emily 85
2010-12-15 John 43

ORDER BY を使って 2010-12-15 のテストで math の点数順に並べる (昇順 (ASCending-order))

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15') ORDER BY math;
date name math
---------- ---------- ----------
2010-12-15 John 43
2010-12-15 Albert 63
2010-12-15 Emily 85

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15') ORDER BY math ASC;
date name math
---------- ---------- ----------
2010-12-15 John 43
2010-12-15 Albert 63
2010-12-15 Emily 85

降順に並べる (DESCending-order)

sqlite> SELECT date, name, math FROM tbl2 WHERE date = date('2010-12-15') ORDER BY math DESC;
date name math
---------- ---------- ----------
2010-12-15 Emily 85
2010-12-15 Albert 63
2010-12-15 John 43