Help us understand the problem. What is going on with this article?

MySQLでGROUP BYした際の、「集約結果毎」の件数と「まとめた結果」の件数の取得方法

More than 1 year has passed since last update.

しばらくハマったのでメモ

ページネーションを付ける時など、GROUP BYで「まとめた結果」の件数を取得したい時に、count(*)だと集約したそれぞれの件数が取れてしまい上手くいきません。

集約結果毎の件数と、まとめた時の全体の件数を知りたい場合の取得方法です。

結論

  • count(*) -> 集約結果毎の件数を取得
MySQL
mysql> select count(*) from table_name group by hoge;
  • count(distinct hoge) -> まとめた結果の件数を取得
MySQL
mysql> select count(distinct hoge) from table_name;

ちょっとだけ説明

# データ
mysql> select * from table_name;
+---+------+
| id| hoge |
+---+------+
| 1 | boo  |
| 2 | boo  |
| 3 | boo  |
| 4 | foo  |
| 5 | foo  |
| 6 | foo  |
| 7 | foo  |
+---+------+
# hoge で group by する
mysql> select * from table_name group by hoge;
+---+------+
| id| hoge |
+---+------+
| 1 | boo  |
| 4 | foo  |
+---+------+

ほしい結果

  • 集約結果毎の件数(boo=>3, foo=>4)
  • まとめた結果の件数(2)

集約結果毎の件数を取得

mysql> select *, count(*) from table_name group by hoge;
+---+------+----------+
| id| hoge | count(*) |
+---+------+----------+
| 1 | boo  |        3 |
| 4 | foo  |        4 |
+---+------+----------+

count(*)を付けるだけでOK。

まとめた結果の件数を取得

mysql> select count(distinct hoge) from table_name;
+----------------------+
| count(distinct hoge) |
+----------------------+
|                    2 |
+----------------------+

重複を除いた値が何種類あるかを取り出せばいいので、 DISTINCT を使います。
group by を付けるとほしい結果が出ないので、別のsqlを書く必要がある。

参考

MySQLでgroup byした結果をカウントする | WP Advisor
https://hacknote.jp/archives/10278/

そのまんまです。

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした