SQL Server/T-SQL Tutorial/Aggregate Functions/SUM — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Версия 13:46, 26 мая 2010
Содержание
HAVING SUM(Salary) > 1000
4> CREATE TABLE employee(
5> id INTEGER NOT NULL PRIMARY KEY,
6> first_name VARCHAR(10),
7> last_name VARCHAR(10),
8> salary DECIMAL(10,2),
9> start_Date DATETIME,
10> region VARCHAR(10),
11> city VARCHAR(20),
12> managerid INTEGER
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3>
4> SELECT SUM(Salary) FROM Employee
5> HAVING SUM(Salary) > 1000
6> GO
----------------------------------------
49549.00
(1 rows affected)
1>
2>
3> drop table employee;
4> GO
1>
2>
Sum function with column alias
3> CREATE TABLE employee(
4> id INTEGER NOT NULL PRIMARY KEY,
5> first_name VARCHAR(10),
6> last_name VARCHAR(10),
7> salary DECIMAL(10,2),
8> start_Date DATETIME,
9> region VARCHAR(10),
10> city VARCHAR(20),
11> managerid INTEGER
12> );
13> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9);
2> GO
(1 rows affected)
1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10);
2> GO
(1 rows affected)
1>
2> select * from employee;
3> GO
id first_name last_name salary start_Date region city managerid
----------- ---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected)
1>
2>
3> SELECT SUM(Salary * ID) As OrderTotalPrice
4> FROM Employee
5> GO
OrderTotalPrice
----------------------------------------
245235.00
(1 rows affected)
1>
2>
3>
4>
5> drop table employee;
6> GO
SUM returns the total value in a column of all selected records or all the DISTINCT (unique) values selected by the query.
This function is used for only number datatypes.
All NULL values are ignored.
5>
6>
7> CREATE TABLE sales(
8> stor_id char(4) NOT NULL,
9> ord_num varchar(20) NOT NULL,
10> ord_date datetime NOT NULL,
11> qty smallint NOT NULL,
12> payterms varchar(12) NOT NULL,
13> title_id varchar(80)
14> )
15> GO
1> insert sales values("1", "QA7442.3", "09/13/94", 75, "ON Billing","1")
2> insert sales values("2", "D4482", "09/14/94", 10, "Net 60", "1")
3> insert sales values("3", "N914008", "09/14/94", 20, "Net 30", "2")
4> insert sales values("4", "N914014", "09/14/94", 25, "Net 30", "3")
5> insert sales values("5", "423LL922", "09/14/94", 15, "ON Billing","3")
6> insert sales values("6", "423LL930", "09/14/94", 10, "ON Billing","2")
7> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> select SUM(qty) from sales;
4> GO
-----------
155
(1 rows affected)
1>
2> drop table sales;
3>
4> GO
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>
SUM with group by
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>
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>