4> create table department(
5> dept_name char(20) not null,
6> emp_cnt int not null,
7> budget float,
8> date_month datetime);
9> 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>
3> SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
4> FROM department
5> GROUP BY emp_cnt, dept_name
6> WITH ROLLUP;
7> GO
dept_name emp_cnt sum_of_budgets
-------------------- ----------- ------------------------
Marketing 3 30000
NULL 3 30000
Accounting 5 10000
Marketing 5 40000
Research 5 115000
NULL 5 165000
Accounting 6 70000
Marketing 6 10000
NULL 6 80000
Accounting 10 40000
Marketing 10 40000
Research 10 70000
NULL 10 150000
NULL NULL 425000
(14 rows affected)
1>
2>
3> drop table department;
4> GO
1>
2>
3>