DB2 小計と合計と割合

DB2 小計と合計と割合

DB2Databaseテンプレート:Category SQL

link

SUM OVER

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

結果

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