回答編集履歴
2
追加
test
CHANGED
|
@@ -16,6 +16,22 @@
|
|
|
16
16
|
|
|
17
17
|
group by 1 order by 1 ;
|
|
18
18
|
|
|
19
|
+
|
|
20
|
+
|
|
21
|
+
select emp.bumon 部門,
|
|
22
|
+
|
|
23
|
+
avg(kyuyo) 部門の平均給与,
|
|
24
|
+
|
|
25
|
+
count(*) 部門の社員数,
|
|
26
|
+
|
|
27
|
+
count(heikin) 平均給与以上の社員数
|
|
28
|
+
|
|
29
|
+
from emp left join (select bumon, avg(kyuyo) heikin from emp group by 1) ave on ave.bumon=emp.bumon and emp.kyuyo>heikin
|
|
30
|
+
|
|
31
|
+
group by 1 order by 1 ;
|
|
32
|
+
|
|
33
|
+
|
|
34
|
+
|
|
19
35
|
```
|
|
20
36
|
|
|
21
37
|
|
1
修正
test
CHANGED
|
@@ -1,20 +1,6 @@
|
|
|
1
1
|
PostgreSQLで作成
|
|
2
2
|
|
|
3
3
|
```sql
|
|
4
|
-
|
|
5
|
-
|
|
6
|
-
|
|
7
|
-
select * from emp;
|
|
8
|
-
|
|
9
|
-
select bumon 部門,
|
|
10
|
-
|
|
11
|
-
avg(kyuyo) 部門の平均給与,
|
|
12
|
-
|
|
13
|
-
count(*) 部門の社員数,
|
|
14
|
-
|
|
15
|
-
sum(case when kyuyo>(select avg(x.kyuyo) from emp x where x.bumon=bumon) then 1 else 0 end) 平均給与以上の社員数
|
|
16
|
-
|
|
17
|
-
from emp group by 1 order by 1 ;
|
|
18
4
|
|
|
19
5
|
|
|
20
6
|
|
|
@@ -40,20 +26,6 @@
|
|
|
40
26
|
|
|
41
27
|
10 | 287500.000000000000 | 8 | 3
|
|
42
28
|
|
|
43
|
-
20 | 305000.000000000000 | 6 | 3
|
|
44
|
-
|
|
45
|
-
30 | 292857.142857142857 | 7 | 4
|
|
46
|
-
|
|
47
|
-
(3 rows)
|
|
48
|
-
|
|
49
|
-
|
|
50
|
-
|
|
51
|
-
部門 | 部門の平均給与 | 部門の社員数 | 平均給与以上の社員数
|
|
52
|
-
|
|
53
|
-
------+---------------------+--------------+----------------------
|
|
54
|
-
|
|
55
|
-
10 | 287500.000000000000 | 8 | 3
|
|
56
|
-
|
|
57
29
|
20 | 305000.000000000000 | 6 | 2
|
|
58
30
|
|
|
59
31
|
30 | 292857.142857142857 | 7 | 4
|