mysql データ集計

・レコードの総件数を調べる count(*)

select count(*) from tossy;

+----------+
| count(*) |
+----------+
| 6 |
+----------+

・フィールドにどんな値が入っているか調べる distinct

select distinct team from tossy;

+--------+
| team |
+--------+
| red |
| blue |
| yellow |
+--------+

------
・最大値を取る max

select max(score) from tossy;

+------------+
| max(score) |
+------------+
| 9.9 |
+------------+

・最小値　min

select min(score) from tossy;

+------------+
| min(score) |
+------------+
| 1.8 |
+------------+

・アベレージ avg

select avg(score) from tossy;

+-------------------+
| avg(score) |
+-------------------+
| 5.866666666666667 |
+-------------------+

・合計　sum

select sum(score) from tossy;

+------------+
| sum(score) |
+------------+
| 35.2 |
+------------+

・グループ毎に、集計する group by

select team, avg(score) from tossy group by team;

+--------+-------------------+
| team | avg(score) |
+--------+-------------------+
| blue | 6.6 |
| red | 5.300000000000001 |
| yellow | 5.699999999999999 |
+--------+-------------------+

・乱数を作る rand()

select * from tossy order by rand() limit 1;

+----+------+--------------+------+-------+---------------------+
| id | name | email | team | score | created |
+----+------+--------------+------+-------+---------------------+
| 2 | to | to@gmail.com | blue | 9.9 | 2016-01-01 15:00:00 |
+----+------+--------------+------+-------+---------------------+