GROUP BYした結果をSUMまたはCOUNTで集計する方法

Question

前提・実現したいこと

SQLに関する質問です。
GROUP BYした結果をSUMまたはCOUNTで集計したいのですが、解決方法が見つかりません。

該当のデータベース

こちらより引用しています。
https://launchpad.net/test-db/employees-db-1

wget https://launchpad.net/test-db/employees-db-1/1.0.6/+download/employees_db-full-1.0.6.tar.bz2
tar jxfv employees_db-full-1.0.6.tar.bz2
cd employees_db
mysql -h"zdb" -P"3306" -u root -p"gci" < employees.sql

show databases;
use employees;

Database
0	TEST1
1	TEST2
2	employees
3	information_schema
4	mysql
5	performance_schema
6	world
Query OK, 0 rows affected.

show tables;

Tables_in_employees
0	departments
1	dept_emp
2	dept_manager
3	employees
4	salaries
5	titles

試したいこと

salalry（給料）の最大が100,000より大きく、かつ最小が90,000以上のemp_noの合計数を出したいのが本来の目的です。
下記のように、まずは条件に合う場合に1,合わない場合に0を出す列を作成しました。
この次SUMやCOUNTを使って集計しようとしたのですが、うまくいきません。

select emp_no,
case when MAX(salary) > 100000 and MIN(salary) >= 90000 then 1 else 0 end as 'ans'
from salaries
group by emp_no
limit 10;

#出力結果
+--------+-----+
| emp_no | ans |
+--------+-----+
|  10001 |   0 |
|  10002 |   0 |
|  10003 |   0 |
|  10004 |   0 |
|  10005 |   0 |
|  10006 |   0 |
|  10007 |   0 |
|  10008 |   0 |
|  10009 |   0 |
|  10010 |   0 |
+--------+-----+
10 rows in set (0.00 sec)

###使用環境
mysql Ver 8.0.23 for Linux on x86_64 (MySQL Community Server - GPL)

Comments

[m.ts10806]

テーブル定義をCREATE TABLE文で
サンプルデータをINSERT文で提示してください。
あと「SQL」とひとえにいってもたくさんありますしバージョンによっても機能が違います。
バージョンも含めて提示してください。質問タグにも追加してください。

[ikegami_m]

失礼しました。バージョン等について追記しました。

[m.ts10806]

タグにも「MySQL」を追加願います。

Answer 1

select count(*)
from (
      select emp_no from salaries
      group by emp_no
      having MAX(salary) > 100000 and MIN(salary) >= 90000
     ) t

Comments

[ikegami_m]

解決しました！ありがとうございました。

[TanakaHiroaki]

大変参考になりました。

Answer 2

集約関数を条件に指定したい場合はHAVING句を利用します。

salalry（給料）の最大が100,000より大きく、かつ最小が90,000以上のemp_noの合計数を出したい

上記の要件であれば、以下のようなSQLで達成できるかと思います。

SELECT emp_no
FROM salaries
GROUP BY emp_no
HAVING MAX(salary) > 100000 and MIN(salary) >= 90000;

Comments

[ikegami_m]

ありがとうございます。上記をselect count(*) from( )で囲うと出力できました。

Answer 3

SQLは初投稿です。以下で目的にあうでしょうか。

select emp_no, COUNT(emp_no)
from salaries
where MAX(salary) > 100000 and MIN(salary) >= 90000
group by emp_no
limit 10;
```＜追記＞
皆さまからの投稿で、上記のように「where句で集約関数の使用」は不可であることを学びました。
どうもありがとうございます。

Comments

[ikegami_m]

ご回答ありがとうございます。
試しましたが、下記のエラーが出てしまいます。
ERROR 1111 (HY000): Invalid use of group function

[TanakaHiroaki]

そうですか。私ではお手上げです。

Answer 4

case式を、そのままHAVINGを使えば良いかと思います。