Mega Code Archive
Group hierarchy using the ROLLUP operator is determined by the order in which the grouping columns are specified
<![CDATA[
5>
6>
7> create table department(
8> dept_name char(20) not null,
9> emp_cnt int not null,
10> budget float,
11> date_month datetime);
12> 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, emp_cnt, SUM(budget) sum_of_budgets
3> FROM department
4> GROUP BY dept_name, emp_cnt
5> WITH ROLLUP;
6> 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
(13 rows affected)
1>
2> drop table department;
3> GO
1>
2>
]]>