8>
9>
10> create table department(
11> dept_name char(20) not null,
12> emp_cnt int not null,
13> budget float,
14> date_month datetime);
15> GO
1>
2> insert into department values('Research', 5, 50000, '01.01.2002');
3> insert into department values('Research', 10, 70000, '01.02.2002');
4> insert into department values('Research', 5, 65000, '01.07.2002');
5> insert into department values('Accounting', 5, 10000, '01.07.2002');
6> insert into department values('Accounting', 10, 40000, '01.02.2002');
7> insert into department values('Accounting', 6, 30000, '01.01.2002');
8> insert into department values('Accounting', 6, 40000, '01.02.2003');
9> insert into department values('Marketing', 6, 10000, '01.01.2003');
10> insert into department values('Marketing', 10, 40000, '01.02.2003');
11> insert into department values('Marketing', 3, 30000, '01.07.2003');
12> insert into department values('Marketing', 5, 40000, '01.01.2003');
13> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> SELECT dept_name, budget, SUM(budget) OVER(PARTITION BY dept_name) as
3> budget_sum,
4> budget/SUM(budget) OVER(PARTITION BY dept_name)* 100 AS percentage
5> FROM department;
6> GO
dept_name budget budget_sum percentage
-------------------- ------------------------ ------------------------ ------------------------
Accounting 10000 120000 8.3333333333333321
Accounting 40000 120000 33.333333333333329
Accounting 30000 120000 25
Accounting 40000 120000 33.333333333333329
Marketing 10000 120000 8.3333333333333321
Marketing 40000 120000 33.333333333333329
Marketing 30000 120000 25
Marketing 40000 120000 33.333333333333329
Research 50000 185000 27.027027027027028
Research 70000 185000 37.837837837837839
Research 65000 185000 35.135135135135137
(11 rows affected)
1>
2>
3> drop table department;
4> GO
1>