データベースの集計関数をまとめておきます。
使用するpostsというテーブルを表示してみます。likesはNULLを許容しているとします。
SELECT * FROM postsmysql -h db -t -u dbuser -pdbpass myapp < main.sql
+----+---------+-------+
| id | message | likes |
+----+---------+-------+
| 1 | post-1 | 12 |
| 2 | post-2 | 8 |
| 3 | post-3 | 11 |
| 4 | post-4 | 3 |
| 5 | post-5 | NULL |
| 6 | post-6 | 9 |
| 7 | post-7 | 4 |
| 8 | post-8 | NULL |
+----+---------+-------+目次
カウント
SELECT COUNT(likes) FROM posts;
SELECT COUNT(id) FROM posts;
SELECT COUNT(*) FROM posts;mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+--------------+
| COUNT(likes) |
+--------------+
| 7 |
+--------------+
+-----------+
| COUNT(id) |
+-----------+
| 9 |
+-----------+
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+合計
SELECT SUM(likes) FROM posts;mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+------------+
| SUM(likes) |
+------------+
| 78 |
+------------+平均
SELECT AVG(likes) FROM posts;mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+------------+
| AVG(likes) |
+------------+
| 11.1429 |
+------------+最大
SELECT MAX(likes) FROM posts;mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+------------+
| MAX(likes) |
+------------+
| 31 |
+------------+最小
SELECT MIN(likes) FROM posts;mysql -h db -t -u dbuser -pdbpass myapp < main.sql
+------------+
| MIN(likes) |
+------------+
| 3 |
+------------+
コメント