SQL:group byとdistinctの同時使用でcount

MySQL 5.0.x

以下の例でcount(*)はcount(foo)と同じ。

全データ

fooとbarの2カラムがあって、それらの値はいくつか重複している。

mysql> select * from hoges;
+----+------+------+---------------------+---------------------+
| id | foo  | bar  | created_at          | updated_at          |
+----+------+------+---------------------+---------------------+
|  1 |    1 |   11 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 | 
|  2 |    2 |   12 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 | 
+----+------+------+---------------------+---------------------+
|  3 |    1 |   11 | 2009-07-27 16:35:33 | 2009-07-27 16:35:33 | 
|  4 |    2 |   12 | 2009-07-27 16:35:33 | 2009-07-27 16:35:33 | 
+----+------+------+---------------------+---------------------+
|  5 |    1 |   21 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 | 
|  6 |    2 |   22 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 | 
+----+------+------+---------------------+---------------------+
|  7 |    3 |   11 | 2009-07-27 16:37:10 | 2009-07-27 16:37:10 | 
|  8 |    4 |   12 | 2009-07-27 16:37:10 | 2009-07-27 16:37:10 | 
+----+------+------+---------------------+---------------------+

countのみ

当然ながら全レコード数8個が得られる。

mysql> select *, count(*) as count from hoges;
+----+------+------+---------------------+---------------------+-------+
| id | foo  | bar  | created_at          | updated_at          | count |
+----+------+------+---------------------+---------------------+-------+
|  1 |    1 |   11 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     8 | 
+----+------+------+---------------------+---------------------+-------+

distinct付きfooでcount

重複するfooを省いてカウントするから、foo=1-4で4個。

mysql> select *, count(distinct foo) as count from hoges;
+----+------+------+---------------------+---------------------+-------+
| id | foo  | bar  | created_at          | updated_at          | count |
+----+------+------+---------------------+---------------------+-------+
|  1 |    1 |   11 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     4 | 
+----+------+------+---------------------+---------------------+-------+

barでgroup byしてからdistinctなしでcount

bar=11,12のレコードはそれぞれ3個あるから3とカウントされる。bar=21,22はそれぞれ1個しかないから1とカウントされる。

mysql> select *, count(*) as count from hoges group by bar;
+----+------+------+---------------------+---------------------+-------+
| id | foo  | bar  | created_at          | updated_at          | count |
+----+------+------+---------------------+---------------------+-------+
|  1 |    1 |   11 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     3 | 
|  2 |    2 |   12 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     3 | 
+----+------+------+---------------------+---------------------+-------+
|  5 |    1 |   21 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 |     1 | 
|  6 |    2 |   22 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 |     1 | 
+----+------+------+---------------------+---------------------+-------+

barでgroup byしたものをdistinct付きfooでcount

id=1と3、id=2と4はbarだけでなくfooも一致するからそれぞれ1つとカウントされて、id=7,8と合計して2となる。

mysql> select *, count(distinct foo) as count from hoges group by bar;
+----+------+------+---------------------+---------------------+-------+
| id | foo  | bar  | created_at          | updated_at          | count |
+----+------+------+---------------------+---------------------+-------+
|  1 |    1 |   11 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     2 | 
|  2 |    2 |   12 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     2 | 
+----+------+------+---------------------+---------------------+-------+
|  5 |    1 |   21 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 |     1 | 
|  6 |    2 |   22 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 |     1 | 
+----+------+------+---------------------+---------------------+-------+

fooとbarの両方でgroup byしてcount

この結果はfooとbarの順番を入れ替えても同じ。

mysql> select *, count(*) as count from hoges group by foo,bar order by id;
+----+------+------+---------------------+---------------------+-------+
| id | foo  | bar  | created_at          | updated_at          | count |
+----+------+------+---------------------+---------------------+-------+
|  1 |    1 |   11 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     2 | 
|  2 |    2 |   12 | 2009-07-27 16:35:23 | 2009-07-27 16:35:23 |     2 | 
+----+------+------+---------------------+---------------------+-------+
|  5 |    1 |   21 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 |     1 | 
|  6 |    2 |   22 | 2009-07-27 16:36:03 | 2009-07-27 16:36:03 |     1 | 
+----+------+------+---------------------+---------------------+-------+
|  7 |    3 |   11 | 2009-07-27 16:37:10 | 2009-07-27 16:37:10 |     1 | 
|  8 |    4 |   12 | 2009-07-27 16:37:10 | 2009-07-27 16:37:10 |     1 | 
+----+------+------+---------------------+---------------------+-------+