【SQL】平均値が大きい順にデータをソートする方法｜AVG + GROUP BY + ORDER BYの使い方と実務パターン

この記事で学べること

• AVG + GROUP BY + ORDER BY DESC の基本構文と使い方
• WHERE句で集計前にデータを絞り込む方法
• HAVING句で集計後に平均値をフィルタする方法
• TOP N / LIMIT で上位N件だけ取得する方法
• JOINと組み合わせて関連テーブルの情報を付加する方法
• サブクエリで平均が最大の部署の全行を取得する方法
• ウィンドウ関数 AVG() OVER(PARTITION BY) の使い方
• MySQL・PostgreSQL・SQL Server・OracleのRDBMS別の違い
• よくあるエラーと対処法

SELECT グループ列, AVG(数値列) AS 別名
FROM テーブル名
GROUP BY グループ列
ORDER BY 別名 DESC;

ポイント：ORDER BY DESC を指定しないとソート順は不定です。平均値が大きい順に並べるには必ず DESC を付けましょう。

SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

実行結果

department | avg_salary
-----------+-----------
開発       | 416666.67
営業       | 310000.00
人事       | 300000.00

SELECT
  department,
  AVG(salary) AS avg_salary,
  COUNT(*) AS cnt
FROM employees
WHERE hire_date >= '2021-01-01'
GROUP BY department
ORDER BY avg_salary DESC;

実行結果

department | avg_salary | cnt
-----------+------------+----
開発       | 380000.00  | 1
営業       | 290000.00  | 2
人事       | 280000.00  | 1

注意：WHERE句では AVG(salary) >= 300000 のような集計関数は使えません。集計結果で絞り込むには次のセクションで紹介する HAVING 句を使います。

SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) >= 300000
ORDER BY avg_salary DESC;

実行結果

department | avg_salary
-----------+-----------
開発       | 416666.67
営業       | 310000.00

SELECT
  department,
  AVG(salary) AS avg_salary,
  COUNT(*) AS cnt
FROM employees
WHERE salary >= 300000
GROUP BY department
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

実行結果

department | avg_salary | cnt
-----------+------------+----
開発       | 416666.67  | 3
営業       | 325000.00  | 2

SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 3;

SELECT TOP 3
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC;

SELECT
  department,
  AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
FETCH FIRST 3 ROWS ONLY;

実行結果（共通）

department | avg_salary
-----------+-----------
開発       | 416666.67
営業       | 310000.00
人事       | 300000.00

SELECT
  d.dept_name,
  d.location,
  AVG(e.salary) AS avg_salary,
  COUNT(*) AS emp_count
FROM employees e
INNER JOIN departments d
  ON e.department = d.dept_code
GROUP BY d.dept_name, d.location
ORDER BY avg_salary DESC;

実行結果

dept_name | location | avg_salary | emp_count
----------+----------+------------+----------
開発部    | 大阪     | 416666.67  | 3
営業部    | 東京     | 310000.00  | 3
人事部    | 東京     | 300000.00  | 2

SELECT *
FROM employees
WHERE department IN (
  SELECT department
  FROM employees
  GROUP BY department
  ORDER BY AVG(salary) DESC
  LIMIT 1
);

実行結果

id | name     | department | salary | hire_date
---+----------+------------+--------+-----------
2  | 鈴木花子 | 開発       | 420000 | 2019-07-15
4  | 高橋美咲 | 開発       | 380000 | 2022-03-20
7  | 渡辺大輔 | 開発       | 450000 | 2018-11-01

SELECT *
FROM employees
WHERE department IN (
  SELECT department
  FROM employees
  GROUP BY department
  HAVING AVG(salary) >= ALL (
    SELECT AVG(salary)
    FROM employees
    GROUP BY department
  )
);

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) AS dept_avg
FROM employees
ORDER BY dept_avg DESC, salary DESC;

実行結果

name     | department | salary | dept_avg
---------+------------+--------+-----------
渡辺大輔 | 開発       | 450000 | 416666.67
鈴木花子 | 開発       | 420000 | 416666.67
高橋美咲 | 開発       | 380000 | 416666.67
田中太郎 | 営業       | 350000 | 310000.00
佐藤一郎 | 営業       | 300000 | 310000.00
中村由美 | 営業       | 280000 | 310000.00
伊藤恵   | 人事       | 320000 | 300000.00
山田健太 | 人事       | 280000 | 300000.00

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER(PARTITION BY department) AS dept_avg,
  DENSE_RANK() OVER(
    ORDER BY AVG(salary) OVER(PARTITION BY department) DESC
  ) AS dept_rank
FROM employees
ORDER BY dept_rank, salary DESC;

注意：上記のネストした OVER 句は一部のRDBMS（MySQLなど）ではエラーになります。その場合はサブクエリで先にウィンドウ関数を計算し、外側のクエリで DENSE_RANK を適用してください。

SELECT
  name,
  department,
  salary,
  dept_avg,
  DENSE_RANK() OVER(ORDER BY dept_avg DESC) AS dept_rank
FROM (
  SELECT
    name,
    department,
    salary,
    AVG(salary) OVER(PARTITION BY department) AS dept_avg
  FROM employees
) sub
ORDER BY dept_rank, salary DESC;

SELECT * FROM (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
  ORDER BY avg_salary DESC
)
WHERE ROWNUM <= 3;

-- NG: WHERE句で集計関数は使えない
SELECT department, AVG(salary)
FROM employees
WHERE AVG(salary) > 300000  -- エラー！
GROUP BY department;

-- OK: HAVING句を使う
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 300000
ORDER BY avg_salary DESC;

実務のコツ：まずは AVG + GROUP BY + ORDER BY DESC の基本形を押さえ、必要に応じて WHERE / HAVING / LIMIT で条件を追加していくのがおすすめです。ウィンドウ関数は「元の行も残したい」場面で活用しましょう。

• 【SQL】AVG関数を使用して平均を求める方法
• 【SQL】MIN関数を使用して最小値を求める方法
• 【SQL】CASE文を使用した複数条件でのデータ更新
• 【SQL】テーブル一覧を取得する方法
• 【SQL】DELETE文を使ってデータベースから行を削除する方法
• 【SQL】指定した項目を全レコード一括で更新する方法
• 【SQL】プライマリキーの追加と削除方法
• 【SQL】CREATE DATABASEでデータベースを作成する方法
• 【Oracle】NOT NULL制約を削除する方法
• 【Oracle】シーケンスの削除方法