SQLのGROUP BYを使ったサブクエリの仕方について

Question

emp表から、部門コード、部門の平均給与、部門別の社員数、給与が部門の平均給与以上の社員数を部門コードの昇順で求めるには、どう記述すればいいでしょうか？ご教授お願いします。

部門コード 部門の平均給与 部門の社員数 平均給与以上の社員数

---

   10       2916.66           3                   1
   20          2175           5                   3
   30       1566.66           6                   2

Answer 1

PostgreSQLで作成

select bumon 部門,
avg(kyuyo) 部門の平均給与,
count(*) 部門の社員数,
sum(case when kyuyo>heikin then 1 else 0 end) 平均給与以上の社員数
from emp join (select bumon, avg(kyuyo) heikin from emp group by 1) ave using(bumon)
 group by 1 order by 1 ;

select emp.bumon 部門,
avg(kyuyo) 部門の平均給与,
count(*) 部門の社員数,
count(heikin) 平均給与以上の社員数
from emp left join (select bumon, avg(kyuyo) heikin from emp group by 1) ave on ave.bumon=emp.bumon and emp.kyuyo>heikin
group by 1 order by 1 ;

部門 | 部門の平均給与 | 部門の社員数 | 平均給与以上の社員数
------+---------------------+--------------+----------------------
10 | 287500.000000000000 | 8 | 3
20 | 305000.000000000000 | 6 | 2
30 | 292857.142857142857 | 7 | 4
(3 rows)

Answer 2

MySQLで簡単に作ってみました。こんな感じで如何でしょうか？

テーブル定義

--
-- テーブルの構造 `emp`
--

CREATE TABLE `emp` (
  `id` int(11) NOT NULL,
  `dep` varchar(1) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  `salary` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- テーブルのデータのダンプ `emp`
--

INSERT INTO `emp` (`id`, `dep`, `salary`) VALUES
(1, 'A', 1000),
(2, 'A', 2000),
(3, 'A', 1500),
(4, 'A', 2000),
(5, 'B', 2000),
(6, 'B', 3000),
(7, 'B', 3000),
(8, 'C', 1000),
(9, 'C', 1200),
(10, 'C', 4000),
(11, 'C', 2000),
(12, 'A', 1700);

抽出するSQL

SELECT emp.dep,count(id),average
from emp
JOIN
(SELECT dep, AVG(salary) as average from emp GROUP by dep) as av on emp.dep = av.dep
 WHERE emp.salary > av.average
 GROUP by emp.dep
 ORDER BY emp.dep

Answer 3

emp表自体が提示されていないのでなんといえませんが
とりあえずgroup by 部門コードしてください

• 平均はsum(給与)/count(*)
• 社員数はcount(*)
• 平均以上の社員はsum(case when sum(給与)/count(*)>給与 then 1 else 0 end)

のようなやり方になります。あとはSQLの種類によって
じゃっかん書き方がかわってくると思います

修正します

構文が間違っていたようです、以下修正します

create table employees(user_id int unique,department_id int,salary int);
insert into employees values
(1001,10,250000),(1002,10,270000),(1003,10,280000),(2001,20,300000),(2002,20,220000),(3001,30,250000);

集計

select t1.department_id
,avg(salary) as avg
,sum(case when t1.salary>t2.avg then 1 else 0 end) avg_up
 from employees as t1
inner join (select
department_id
,avg(salary) as avg
from
employees
group by
department_id
)  as t2 on t1.department_id=t2.department_id
group by department_id

Comments

[退会済みユーザー]

select
department_id
,sum(salary)/count(*)
,sum(case when sum(salary)/count(*)> salary then 1 else 0 end)
from
employees
group by
department_id;

と記述したところ、単一グループのグループ関数ではありません。とエラーが出ました。。
select句には集計関数とdepartment_idが入っているのにどうしてエラーが出るのかもわかりません。。
SQLはORACLE SQLです。

[yambejp]

ちょっと間違ってました、追記しておきます

[yambejp]

退会するくらいなら質問を締めていけばいいのに・・・と思いました