Mega Code Archive

 

Categories / MSSQL Tutorial / Aggregate Functions

 

Sum with CUBE

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>