Mega Code Archive

 

Categories / MSSQL Tutorial / Aggregate Functions

 

SUM with WITH ROLLUP

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>