SQLのwhere句でcount関数を条件に使うことはできるのでしょうか？
SQLのwhere句でcountを使う
emp_no（社員番号）をカウントして3万人以上の社員がいる部署を部署テーブル（departments）と社員・部署紐付けテーブル（dept_emp)から抽出しようとしたところ、where句でcountを使うと、syntaxエラーとなりました。
以下はMySQLでの実行例です。一回目のSQLはwhere句なしで、すべての部署の社員数を抽出しています。二回目のSQLでwhere句にcount関数を使った部分がエラーとなっています。
SELECT dept_name, count(emp_no) FROM `dept_emp` left join departments on dept_emp.dept_no = departments.dept_no group by dept_emp.dept_no; +--------------------+---------------+ | dept_name | count(emp_no) | +--------------------+---------------+ | Marketing | 20211 | | Finance | 17346 | | Human Resources | 17786 | | Production | 73485 | | Development | 85707 | | Quality Management | 20117 | | Sales | 52245 | | Research | 21126 | | Customer Service | 23580 | +--------------------+---------------+ 9 rows in set (0.12 sec) SELECT dept_name, count(emp_no) FROM `dept_emp` left join departments on dept_emp.dept_no = departments.dept_no group by dept_emp.dept_no where count(emp_no) > 30000; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'where count(emp_no) > 30000' at line 2
実は、countなどの集計関数を単純にwhereで扱うことは出来ないんですね。絞り込み条件にcountを組み込みたい場合は、havingを使います。
参考）count関数の値をwhere句で使用する方法について| OKWAVE
以下のようにhavingを使って記述すると、社員3万人以上の部署名（dept_name）一覧が抽出できました。
SELECT dept_name, count(emp_no) FROM `dept_emp` left join departments on dept_emp.dept_no = departments.dept_no group by dept_emp.dept_no having count(emp_no) > 30000; +-------------+---------------+ | dept_name | count(emp_no) | +-------------+---------------+ | Production | 73485 | | Development | 85707 | | Sales | 52245 | +-------------+---------------+ 3 rows in set (0.79 sec)
count関数の引数内に３値論理式で条件を指定する方法
havingを使う以外に、count関数の引数内に条件を記述することも可能です。３値論理式は、真と偽の他に第三の「不明」という値を取ります。
参考）3値論理とNULL (1/3)：CodeZine（コードジン）
３値論理式は直感的に理解しづらいのですが、目的の動作をさせるために非常に短いコードで記述が可能です。
以下のようにcount関数の引数に条件を記述すると、全社員のうち、性別が男性（gender=’M’）の数をカウントできます。
mysql> select count(gender='M' or null) from employees; +---------------------------+ | count(gender='M' or null) | +---------------------------+ | 179973 | +---------------------------+ 1 row in set (0.08 sec)
【関連記事】
▶SQL countの条件は引数内に記述できる 単クエリで複数の件数を取得する方法
employees（社員テーブル）にはこのようなデータが入っています。group byでgender（性別）を指定すると、それぞれの性別のカウントが取得できます。
mysql> select * from employees limit 10; +--------+------------+------------+-----------+--------+------------+ | emp_no | birth_date | first_name | last_name | gender | hire_date | +--------+------------+------------+-----------+--------+------------+ | 10001 | 1953-09-02 | Georgi | Facello | M | 1986-06-26 | | 10002 | 1964-06-02 | Bezalel | Simmel | F | 1985-11-21 | | 10003 | 1959-12-03 | Parto | Bamford | M | 1986-08-28 | | 10004 | 1954-05-01 | Chirstian | Koblick | M | 1986-12-01 | | 10005 | 1955-01-21 | Kyoichi | Maliniak | M | 1989-09-12 | | 10006 | 1953-04-20 | Anneke | Preusig | F | 1989-06-02 | | 10007 | 1957-05-23 | Tzvetan | Zielinski | F | 1989-02-10 | | 10008 | 1958-02-19 | Saniya | Kalloufi | M | 1994-09-15 | | 10009 | 1952-04-19 | Sumant | Peac | F | 1985-02-18 | | 10010 | 1963-06-01 | Duangkaew | Piveteau | F | 1989-08-24 | +--------+------------+------------+-----------+--------+------------+ 10 rows in set (0.00 sec) mysql> select count(*), gender from employees group by gender; +----------+--------+ | count(*) | gender | +----------+--------+ | 179974 | M | | 120051 | F | +----------+--------+ 2 rows in set (0.17 sec)
直感的には、count(gender=’M’)を指定すれば目的の値が抽出できそうですが、結果はこうなります。
mysql> select count(gender='M') from employees; +-------------------+ | count(gender='M') | +-------------------+ | 300025 | +-------------------+ 1 row in set (0.04 sec)
gender=’M’と指定しているのに、count(*)と同じ値が返ってきてしまうんですね。エラーも出ませんし、一見正しく見えてしまうので開発現場ではバグ発生の原因になることも考えられます。
多くの人がコードをメンテナンスするようなケースで３値論理式を使用する際には、コメントを付与するなど、工夫するのが良いでしょう。
SQLのwhere句でcountを使う方法のまとめ
- SQLのwhereで単純にcount関数を使うとsyntaxエラーになる
- 絞り込み条件にcountを使いたい場合はhavingを利用する
- countの引数に３値論理式を使うことで、カウント対象の条件を指定可能
