5>
6> create table department(
7> dept_name char(20) not null,
8> emp_cnt int not null,
9> budget float,
10> date_month datetime);
11> 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,
3> SUM(emp_cnt) OVER(PARTITION BY dept_name) AS emp_cnt_sum,
4> AVG(budget) OVER(PARTITION BY dept_name) AS budget_avg,
5> COUNT(dept_name) OVER(PARTITION BY dept_name) AS dept_cnt
6> FROM department;
7> GO
dept_name budget emp_cnt_sum budget_avg dept_cnt
-------------------- ------------------------ ----------- ------------------------ -----------
Accounting 10000 27 30000 4
Accounting 40000 27 30000 4
Accounting 30000 27 30000 4
Accounting 40000 27 30000 4
Marketing 10000 24 30000 4
Marketing 40000 24 30000 4
Marketing 30000 24 30000 4
Marketing 40000 24 30000 4
Research 50000 20 61666.666666666664 3
Research 70000 20 61666.666666666664 3
Research 65000 20 61666.666666666664 3
(11 rows affected)
1>
2>
3> drop table department;
4> GO
1>
2>