- 追加された行はこのように表示されます。
- 削除された行は
このように表示されます。
!!!DB2 小計と合計と割合
[DB2][Database]{{category SQL}}
{{amazon 4873113156}}
!SUM OVER
select dept_total, total, (dept_total * 100.00 / total) as pct from(
select workdept, dept_total, total, (dept_total * 100.00 / total) as pct from(
select distinct
workdept,
sum(salary) over(partition by workdept) as dept_total,
sum(salary) over() total
from employee
)
order by workdept
::結果
DEPT_TOTAL TOTAL PCT
---------- ---------- -----
354250.00 2442525.00 14.50
94250.00 2442525.00 3.85
308890.00 2442525.00 12.64
646620.00 2442525.00 26.47
358680.00 2442525.00 14.68
80175.00 2442525.00 3.28
317140.00 2442525.00 12.98
282520.00 2442525.00 11.56
WORKDEPT DEPT_TOTAL TOTAL PCT
-------- ---------- ---------- -----
A00 354250.00 2442525.00 14.50
B01 94250.00 2442525.00 3.85
C01 308890.00 2442525.00 12.64
D11 646620.00 2442525.00 26.47
D21 358680.00 2442525.00 14.68
E01 80175.00 2442525.00 3.28
E11 317140.00 2442525.00 12.98
E21 282520.00 2442525.00 11.56