Mega Code Archive
SUM with group by
<![CDATA[
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 dept_name, emp_cnt;
6> GO
dept_name emp_cnt sum_of_budgets
-------------------- ----------- ------------------------
Marketing 3 30000
Accounting 5 10000
Marketing 5 40000
Research 5 115000
Accounting 6 70000
Marketing 6 10000
Accounting 10 40000
Marketing 10 40000
Research 10 70000
(9 rows affected)
1>
2> drop table department;
3> GO
1>
2>
]]>