Group hierarchy using the ROLLUP operator is determined by the order in which the grouping columns are specified. : ROLLUP « Analytical Functions « SQL Server / T-SQL Tutorial

1. SQL Server / T-SQL Tutorial
2. Analytical Functions
3. ROLLUP

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>