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>
4> SELECT dept_name, emp_cnt, SUM(budget) sum_of_budgets
5> FROM department
6> GROUP BY dept_name, emp_cnt
7> WITH CUBE;
8> GO
dept_name emp_cnt sum_of_budgets
-------------------- ----------- ------------------------
Accounting 5 10000
Accounting 6 70000
Accounting 10 40000
Accounting NULL 120000
Marketing 3 30000
Marketing 5 40000
Marketing 6 10000
Marketing 10 40000
Marketing NULL 120000
Research 5 115000
Research 10 70000
Research NULL 185000
NULL NULL 425000
NULL 3 30000
NULL 5 165000
NULL 6 80000
NULL 10 150000
(17 rows affected)
1>
2>
3> drop table department;
4> GO
1>
2>