SQL Server/T-SQL/Analytical Functions/COMPUTE
Содержание
COMPUTE: calculate summary values that appear as additional rows in the result of a query
11>
12> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
13> project_no CHAR(4) NOT NULL,
14> job CHAR (15) NULL,
15> enter_date DATETIME NULL)
16>
17> insert into works_on values (1, "p1", "analyst", "1997.10.1")
18> insert into works_on values (1, "p3", "manager", "1999.1.1")
19> insert into works_on values (2, "p2", "clerk", "1998.2.15")
20> insert into works_on values (2, "p2", NULL, "1998.6.1")
21> insert into works_on values (3, "p2", NULL, "1997.12.15")
22> insert into works_on values (4, "p3", "analyst", "1998.10.15")
23> insert into works_on values (5, "p1", "manager", "1998.4.15")
24> insert into works_on values (6, "p1", NULL, "1998.8.1")
25> insert into works_on values (7, "p2", "clerk", "1999.2.1")
26> insert into works_on values (8, "p3", "clerk", "1997.11.15")
27> insert into works_on values (7, "p1", "clerk", "1998.1.4")
28> -- COMPUTE clause uses aggregate functions (MIN, MAX, SUM, AVG, and COUNT) to
calculate summary values that appear as additional rows in the result of a query.
29>
30> SELECT emp_no, project_no, enter_date
31> FROM works_on
32> WHERE project_no = "p1" OR project_no = "p2"
33> COMPUTE MIN(enter_date)
34> 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)
emp_no project_no enter_date
----------- ---------- -----------------------
1 p1 1997-10-01 00:00:00.000
2 p2 1998-02-15 00:00:00.000
2 p2 1998-06-01 00:00:00.000
3 p2 1997-12-15 00:00:00.000
5 p1 1998-04-15 00:00:00.000
6 p1 1998-08-01 00:00:00.000
7 p2 1999-02-01 00:00:00.000
7 p1 1998-01-04 00:00:00.000
min
=======================
1997-10-01 00:00:00.000
1>
2> drop table works_on
3> GO
COMPUTE MIN date
12>
13> CREATE TABLE works_on (emp_no INTEGER NOT NULL,
14> project_no CHAR(4) NOT NULL,
15> job CHAR (15) NULL,
16> enter_date DATETIME NULL)
17>
18> insert into works_on values (1, "p1", "analyst", "1997.10.1")
19> insert into works_on values (1, "p3", "manager", "1999.1.1")
20> insert into works_on values (2, "p2", "clerk", "1998.2.15")
21> insert into works_on values (2, "p2", NULL, "1998.6.1")
22> insert into works_on values (3, "p2", NULL, "1997.12.15")
23> insert into works_on values (4, "p3", "analyst", "1998.10.15")
24> insert into works_on values (5, "p1", "manager", "1998.4.15")
25> insert into works_on values (6, "p1", NULL, "1998.8.1")
26> insert into works_on values (7, "p2", "clerk", "1999.2.1")
27> insert into works_on values (8, "p3", "clerk", "1997.11.15")
28> insert into works_on values (7, "p1", "clerk", "1998.1.4")
29>
30> select * from works_on
31> 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)
emp_no project_no job enter_date
----------- ---------- --------------- -----------------------
1 p1 analyst 1997-10-01 00:00:00.000
1 p3 manager 1999-01-01 00:00:00.000
2 p2 clerk 1998-02-15 00:00:00.000
2 p2 NULL 1998-06-01 00:00:00.000
3 p2 NULL 1997-12-15 00:00:00.000
4 p3 analyst 1998-10-15 00:00:00.000
5 p1 manager 1998-04-15 00:00:00.000
6 p1 NULL 1998-08-01 00:00:00.000
7 p2 clerk 1999-02-01 00:00:00.000
8 p3 clerk 1997-11-15 00:00:00.000
7 p1 clerk 1998-01-04 00:00:00.000
(11 rows affected)
1>
2>
3> SELECT emp_no, project_no, enter_date
4> FROM works_on
5> WHERE project_no = "p1" OR project_no = "p2"
6> ORDER BY project_no
7> COMPUTE MIN(enter_date) BY project_no
8> GO
emp_no project_no enter_date
----------- ---------- -----------------------
1 p1 1997-10-01 00:00:00.000
5 p1 1998-04-15 00:00:00.000
6 p1 1998-08-01 00:00:00.000
7 p1 1998-01-04 00:00:00.000
min
-----------------------
1997-10-01 00:00:00.000
emp_no project_no enter_date
----------- ---------- -----------------------
7 p2 1999-02-01 00:00:00.000
2 p2 1998-02-15 00:00:00.000
2 p2 1998-06-01 00:00:00.000
3 p2 1997-12-15 00:00:00.000
min
-----------------------
1997-12-15 00:00:00.000
1>
2> drop table works_on
3> GO
1>
COMPUTE SUM(Salary)
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> -- COMPUTE BY
3>
4> SELECT salary, Name
5> FROM Employee
6> ORDER BY ID, Name
7> COMPUTE SUM(Salary)
8> GO
salary Name
----------- ----------
40420 Jason
14420 Robert
24020 Celia
40620 Linda
80026 David
70060 James
90620 Alison
26020 Chris
60020 Mary
sum
===========
446226
1>
2> drop table employee
3> GO
1>
COMPUTE SUM(Salary) By Name
1> create table employee(
2> ID int,
3> name nvarchar (10),
4> salary int,
5> start_date datetime,
6> city nvarchar (10),
7> region char (1))
8> GO
1>
2> insert into employee (ID, name, salary, start_date, city, region)
3> values (1, "Jason", 40420, "02/01/94", "New York", "W")
4> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (2, "Robert",14420, "01/02/95", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (4, "Linda", 40620, "11/04/97", "New York", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (5, "David", 80026, "10/05/98", "Vancouver","W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (6, "James", 70060, "09/06/99", "Toronto", "N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (7, "Alison",90620, "08/07/00", "New York", "W")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N")
3> GO
(1 rows affected)
1> insert into employee (ID, name, salary, start_date, city, region)
2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W")
3> GO
(1 rows affected)
1>
2> select * from employee
3> GO
ID name salary start_date city region
----------- ---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected)
1>
2> SELECT ID, Name, City, Salary
3> FROM Employee
4> WHERE ID > 2
5> ORDER BY Name, City
6> COMPUTE SUM(Salary) By Name
7> GO
ID Name City Salary
----------- ---------- ---------- -----------
7 Alison New York 90620
sum
-----------
90620
ID Name City Salary
----------- ---------- ---------- -----------
3 Celia Toronto 24020
sum
-----------
24020
ID Name City Salary
----------- ---------- ---------- -----------
8 Chris Vancouver 26020
sum
-----------
26020
ID Name City Salary
----------- ---------- ---------- -----------
5 David Vancouver 80026
sum
-----------
80026
ID Name City Salary
----------- ---------- ---------- -----------
6 James Toronto 70060
sum
-----------
70060
ID Name City Salary
----------- ---------- ---------- -----------
4 Linda New York 40620
sum
-----------
40620
ID Name City Salary
----------- ---------- ---------- -----------
9 Mary Toronto 60020
sum
-----------
60020
1>
2> drop table employee
3> GO
1>
Use multiple aggregate functions in a COMPUTE clause
1>
2> CREATE TABLE project (project_no CHAR(4) NOT NULL,
3> project_name CHAR(15) NOT NULL,
4> budget FLOAT NULL)
5> GO
1> insert into project values ("p1", "Search Engine", 120000.00)
2> insert into project values ("p2", "Programming", 95000.00)
3> insert into project values ("p3", "SQL", 186500.00)
4> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1> select * from project
2> GO
project_no project_name budget
---------- --------------- ------------------------
p1 Search Engine 120000
p2 Programming 95000
p3 SQL 186500
(3 rows affected)
1>
2> -- Use multiple aggregate functions in a COMPUTE clause.
3>
4> SELECT project_no, budget
5> FROM project
6> WHERE budget < 150000
7> COMPUTE SUM(budget), AVG(budget)
8>
9> drop table project
10> GO
project_no budget
---------- ------------------------
p1 120000
p2 95000
sum
========================
215000
avg
========================
107500
1>