SQL Server/T-SQL Tutorial/Analytical Functions/COMPUTE
Содержание
COMPUTE MIN(start_date) BY region
<source lang="sql">
2> CREATE TABLE employee( 3> id INTEGER NOT NULL PRIMARY KEY, 4> first_name VARCHAR(10), 5> last_name VARCHAR(10), 6> salary DECIMAL(10,2), 7> start_Date DATETIME, 8> region VARCHAR(10), 9> city VARCHAR(20), 10> managerid INTEGER 11> ); 12> 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> SELECT * 2> FROM Employee 3> ORDER BY region 4> COMPUTE MIN(start_date) BY region 5> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
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
min
-----------------------
2002-05-19 00:00:00.000
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
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
min
-----------------------
2001-04-16 00:00:00.000
id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
min
-----------------------
2003-07-21 00:00:00.000
id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
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
min
-----------------------
2007-07-17 00:00:00.000
1> drop table employee; 2> GO</source>
The use of multiple aggregate functions in a COMPUTE clause.
<source lang="sql">
4> 5> 6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20), 14> managerid INTEGER 15> ); 16> 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> SELECT * 3> FROM Employee 4> COMPUTE SUM(salary), AVG(salary) 5> 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
sum
----------------------------------------
49549.00
avg
----------------------------------------
5505.444444
1> drop table employee; 2> GO</source>
The use of the COMPUTE clause, with and without the BY portion.
<source lang="sql">
4> 5> 6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20), 14> managerid INTEGER 15> ); 16> 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> SELECT * 3> FROM employee 4> COMPUTE MIN(start_date) 5> 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
min
-----------------------
2001-04-16 00:00:00.000
1> 2> drop table employee; 3> GO</source>
Using COMPUTE with aggregate functions.
<source lang="sql">
5> CREATE TABLE Classification ( 6> Classif_ID integer NOT NULL PRIMARY KEY, 7> Classification varchar(25)) 8> GO 1> 2> INSERT into Classification VALUES( 1,"Pop") 3> INSERT into Classification VALUES( 2,"Country") 4> INSERT into Classification VALUES( 3,"Alternative") 5> INSERT into Classification VALUES( 4,"Metal") 6> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE CD ( 4> CD_ID integer NOT NULL PRIMARY KEY, 5> CD_Title varchar(40), 6> Composer_ID integer NOT NULL, 7> Classif_ID integer NOT NULL, 8> SalesPrice money, 9> AverageCost money) 10> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> 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 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 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 CD.Classif_ID,Classification.Classification, 4> AVG(CD.SalesPrice) "Average Selling Price" 5> FROM CD,Classification 6> WHERE CD.Classif_ID = Classification.Classif_ID 7> GROUP BY CD.Classif_ID,Classification.Classification 8> ORDER BY Classification.Classification 9> COMPUTE MIN(AVG(CD.SalesPrice)) 10> GO Classif_ID Classification Average Selling Price
------------------------- ---------------------
3 Alternative 15.3233
2 Country 15.4900
4 Metal 14.1328
1 Pop 14.9900
min
---------------------
14.1328
1> 2> 3> 4> drop table Classification; 5> 6> drop table CD; 7> GO</source>
using the COMPUTE...BY to provide subtotals
<source lang="sql">
You must also use the ORDER BY clause, and all columns in the Compute...BY clause must appear in the ORDER BY clause. 6> CREATE TABLE Classification ( 7> Classif_ID integer NOT NULL PRIMARY KEY, 8> Classification varchar(25)) 9> GO 1> 2> INSERT into Classification VALUES( 1,"Pop") 3> INSERT into Classification VALUES( 2,"Country") 4> INSERT into Classification VALUES( 3,"Alternative") 5> INSERT into Classification VALUES( 4,"Metal") 6> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> 2> 3> CREATE TABLE CD ( 4> CD_ID integer NOT NULL PRIMARY KEY, 5> CD_Title varchar(40), 6> Composer_ID integer NOT NULL, 7> Classif_ID integer NOT NULL, 8> SalesPrice money, 9> AverageCost money) 10> GO 1> INSERT into CD VALUES(2000,"John",100,1,16.99,6.99) 2> INSERT into CD VALUES(2001,"Chicago 16",107,1,14.99,5.99) 3> INSERT into CD VALUES(2002,"Chicago 17",107,1,14.99,5.99) 4> INSERT into CD VALUES(2003,"Chicago 18",107,1,14.99,5.99) 5> INSERT into CD VALUES(2004,"Greatest Hits",107,1,16.99,7.99) 6> INSERT into CD VALUES(2005,"Midnight",101,3,14.99,5.99) 7> INSERT into CD VALUES(2006,"Mode",115,3,14.99,5.99) 8> INSERT into CD VALUES(2007,"Ultra",115,3,15.99,5.99) 9> INSERT into CD VALUES(2008,"Mindcrime",102,4,14.99,5.99) 10> INSERT into CD VALUES(2009,"Empire",102,4,14.99,5.99) 11> INSERT into CD VALUES(2010,"Land",102,4,12.99,4.99) 12> INSERT into CD VALUES(2011,"Night",103,4,11.99,3.99) 13> INSERT into CD VALUES(2012,"Pyromania",103,4,14.99,5.99) 14> INSERT into CD VALUES(2013,"Hysteria",103,4,14.99,5.99) 15> INSERT into CD VALUES(2014,"Hits",103,4,13.99,4.99) 16> INSERT into CD VALUES(2015,"Hits 2",104,2,15.99,6.99) 17> INSERT into CD VALUES(2016,"Greatest",105,2,14.99,5.99) 18> INSERT into CD VALUES(2017,"Hits 3",106,1,13.99,5.99) 19> INSERT into CD VALUES(2018,"Deep",108,1,12.99,2.99) 20> INSERT into CD VALUES(2019,"Turning",109,1,14.99,5.99) 21> INSERT into CD VALUES(2020,"TheHits",109,1,16.99,7.99) 22> INSERT into CD VALUES(2021,"Cars",110,1,9.99,3.99) 23> INSERT into CD VALUES(2022,"Anthology",110,1,25.99,11.99) 24> INSERT into CD VALUES(2023,"City",110,1,14.99,5.99) 25> INSERT into CD VALUES(2024,"Rick",111,1,11.99,2.99) 26> INSERT into CD VALUES(2025,"Live",112,1,19.99,8.99) 27> INSERT into CD VALUES(2026,"Pat",113,1,16.99,6.99) 28> INSERT into CD VALUES(2027,"Big",114,1,14.99,5.99) 29> INSERT into CD VALUES(2028,"Hurting",114,1,11.99,3.99) 30> INSERT into CD VALUES(2029,"Vol 1",116,1,9.99,2.99) 31> INSERT into CD VALUES(2030,"Vol 2",116,1,9.99,2.99) 32> 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 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 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 CD.Classif_ID,Classification.Classification, 4> CD.SalesPrice 5> FROM CD,Classification 6> WHERE CD.Classif_ID = Classification.Classif_ID 7> ORDER BY Classification.Classification 8> COMPUTE MIN(CD.SalesPrice) by Classification.Classification 9> GO Classif_ID Classification SalesPrice
------------------------- ---------------------
3 Alternative 14.9900
3 Alternative 14.9900
3 Alternative 15.9900
min
---------------------
14.9900
Classif_ID Classification SalesPrice
------------------------- ---------------------
2 Country 15.9900
2 Country 14.9900
min
---------------------
14.9900
Classif_ID Classification SalesPrice
------------------------- ---------------------
4 Metal 14.9900
4 Metal 14.9900
4 Metal 12.9900
4 Metal 11.9900
4 Metal 14.9900
4 Metal 14.9900
4 Metal 13.9900
min
---------------------
11.9900
Classif_ID Classification SalesPrice
------------------------- ---------------------
1 Pop 16.9900
1 Pop 14.9900
1 Pop 14.9900
1 Pop 14.9900
1 Pop 16.9900
1 Pop 13.9900
1 Pop 12.9900
1 Pop 14.9900
1 Pop 16.9900
1 Pop 9.9900
1 Pop 25.9900
1 Pop 14.9900
1 Pop 11.9900
1 Pop 19.9900
1 Pop 16.9900
1 Pop 14.9900
1 Pop 11.9900
1 Pop 9.9900
1 Pop 9.9900
min
---------------------
9.9900
1> 2> drop table Classification; 3> drop table CD; 4> GO</source>
