Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationEventAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
46
Help us understand the problem. What is going on with this article?

More than 1 year has passed since last update.

@96kuroguro

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

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

ページネーションを付ける時など、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/

そのまんまです。

46
Help us understand the problem. What is going on with this article?
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
46
Help us understand the problem. What is going on with this article?