SQL Server/T-SQL Tutorial/Aggregate Functions/Aggregate function
Содержание
Aggregate function in sub query
<source lang="sql">
4> IF OBJECT_ID("dbo.Sessions") IS NOT NULL 5> DROP TABLE dbo.Sessions; 6> GO 1> 2> CREATE TABLE dbo.Sessions 3> ( 4> keycol INT NOT NULL IDENTITY PRIMARY KEY, 5> app VARCHAR(10) NOT NULL, 6> usr VARCHAR(10) NOT NULL, 7> starttime DATETIME NOT NULL, 8> endtime DATETIME NOT NULL, 9> CHECK(endtime > starttime) 10> ); 11> GO 1> 2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 08:30", "20060212 10:30"); 3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 08:30", "20060212 08:45"); 4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:00", "20060212 09:30"); 5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 09:15", "20060212 10:30"); 6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:15", "20060212 09:30"); 7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 10:30", "20060212 14:30"); 8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 10:45", "20060212 11:30"); 9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 11:00", "20060212 12:30"); 10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 08:30", "20060212 08:45"); 11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 09:00", "20060212 09:30"); 12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 11:45", "20060212 12:00"); 13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 12:30", "20060212 14:00"); 14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 12:45", "20060212 13:30"); 15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 13:00", "20060212 14:00"); 16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 14:00", "20060212 16:30"); 17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 15:30", "20060212 17:00"); 18> GO 1> 2> SELECT app, MAX(cnt) AS mx 3> FROM (SELECT app, 4> (SELECT COUNT(*) FROM dbo.Sessions AS C 5> WHERE ts >= starttime 6> AND ts < endtime) AS cnt 7> FROM (SELECT DISTINCT app, starttime AS ts 8> FROM dbo.Sessions) AS T) AS D 9> GROUP BY app; 10> GO app mx
-----------
app1 5 app2 4 1> 2> drop table sessions; 3> GO 1> 2></source>
Aggregate functions are applied to a group of data values from a column.
<source lang="sql">
Aggregate functions always return a single value. The Transact-SQL language supports six aggregate functions: AVG MAX MIN SUM COUNT COUNT_BIG AVG (short for average) calculates the arithmetic mean of the data values contained within a column. The column must contain numeric values. MAX calculates the maximum, and MIN the minimum, data value of the column. The column can contain numeric, string, and date/time values. SUM calculates the total of all data values in a column. The column must contain numeric values. COUNT calculates the number of (non-null) data values in a column. The only aggregate function not being applied to columns is COUNT(*). This function returns the number of rows (whether or not particular columns have null values). COUNT_BIG function is analogous to the COUNT function. The only difference is that COUNT_BIG returns a value of the BIGINT data type.</source>
Query with aggregate function as a column
<source lang="sql">
4> IF OBJECT_ID("dbo.Sessions") IS NOT NULL 5> DROP TABLE dbo.Sessions; 6> GO 1> 2> CREATE TABLE dbo.Sessions 3> ( 4> keycol INT NOT NULL IDENTITY PRIMARY KEY, 5> app VARCHAR(10) NOT NULL, 6> usr VARCHAR(10) NOT NULL, 7> starttime DATETIME NOT NULL, 8> endtime DATETIME NOT NULL, 9> CHECK(endtime > starttime) 10> ); 11> GO 1> 2> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 08:30", "20060212 10:30"); 3> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 08:30", "20060212 08:45"); 4> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:00", "20060212 09:30"); 5> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 09:15", "20060212 10:30"); 6> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 09:15", "20060212 09:30"); 7> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 10:30", "20060212 14:30"); 8> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user1", "20060212 10:45", "20060212 11:30"); 9> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app1", "user2", "20060212 11:00", "20060212 12:30"); 10> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 08:30", "20060212 08:45"); 11> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 09:00", "20060212 09:30"); 12> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 11:45", "20060212 12:00"); 13> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 12:30", "20060212 14:00"); 14> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 12:45", "20060212 13:30"); 15> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 13:00", "20060212 14:00"); 16> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user1", "20060212 14:00", "20060212 16:30"); 17> INSERT INTO dbo.Sessions(app, usr, starttime, endtime) VALUES("app2", "user2", "20060212 15:30", "20060212 17:00"); 18> GO 1> 2> 3> SELECT app, 4> (SELECT COUNT(*) FROM dbo.Sessions AS C 5> WHERE ts >= starttime 6> AND ts < endtime) AS cnt 7> FROM (SELECT DISTINCT app, starttime AS ts 8> FROM dbo.Sessions) AS T; 9> 10> GO app cnt
-----------
app1 3 app1 3 app1 5 app1 1 app1 2 app1 3 app2 3 app2 3 app2 3 app2 2 app2 3 app2 4 app2 2 app2 2 1> 2> drop table sessions; 3> GO 1></source>
Using Aliase with aggregate function
<source lang="sql">
20> 21> CREATE TABLE employee( 22> id INTEGER NOT NULL PRIMARY KEY, 23> first_name VARCHAR(10), 24> last_name VARCHAR(10), 25> salary DECIMAL(10,2), 26> start_Date DATETIME, 27> region VARCHAR(10), 28> city VARCHAR(20) 29> ); 30> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina"); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto"); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city
---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected) 1> 2> 3> select AVG(DISTINCT Salary) AS "Average Distinct Salary" from employee; 4> GO Average Distinct Salary
5505.444444
(1 rows affected) 1> 2> 3> 4> 5> drop table employee; 6> GO 1></source>
Using calculations within aggregate functions.
<source lang="sql">
5> 6> CREATE TABLE CD ( 7> CD_ID integer NOT NULL PRIMARY KEY, 8> CD_Title varchar(40), 9> Composer_ID integer NOT NULL, 10> Classif_ID integer NOT NULL, 11> SalesPrice money, 12> AverageCost money) 13> 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> CREATE TABLE Inventory( 2> CD_ID integer NOT NULL, 3> StoreID integer NOT NULL, 4> QOH integer NULL, 5> Returns integer NULL, 6> QtyOrdered integer NULL, 7> Discontinued char(1) NOT NULL) 8> GO 1> INSERT into Inventory VALUES(2000,1330,10,0,0,"N") 2> INSERT into Inventory VALUES(2015,1330,5,0,3,"N") 3> INSERT into Inventory VALUES(2016,1330,6,0,11,"N") 4> INSERT into Inventory VALUES(2017,1330,5,2,0,"N") 5> INSERT into Inventory VALUES(2000,1040,3,0,9,"N") 6> INSERT into Inventory VALUES(2015,1040,1,0,10,"N") 7> INSERT into Inventory VALUES(2016,1040,4,0,11,"N") 8> INSERT into Inventory VALUES(2018,1040,15,8,0,"N") 9> INSERT into Inventory VALUES(2019,1040,22,3,0,"N") 10> INSERT into Inventory VALUES(2020,1040,10,0,0,"N") 11> INSERT into Inventory VALUES(2021,1040,19,0,NULL,"N") 12> INSERT into Inventory VALUES(2008,1200,11,0,NULL,"N") 13> INSERT into Inventory VALUES(2009,1200,5,0,9,"N") 14> INSERT into Inventory VALUES(2011,1210,18,0,NULL,"N") 15> INSERT into Inventory VALUES(2012,1210,5,0,NULL,"N") 16> INSERT into Inventory VALUES(2013,1210,1,0,6,"N") 17> INSERT into Inventory VALUES(2025,1220,3,0,8,"N") 18> INSERT into Inventory VALUES(2026,1220,3,0,7,"N") 19> INSERT into Inventory VALUES(2019,1300,2,0,8,"N") 20> INSERT into Inventory VALUES(2020,1300,9,0,0,"N") 21> INSERT into Inventory VALUES(2027,1310,2,0,8,"N") 22> INSERT into Inventory VALUES(2028,1310,4,0,4,"N") 23> INSERT into Inventory VALUES(2029,1320,2,0,8,"N") 24> INSERT into Inventory VALUES(2030,1320,6,0,0,"N") 25> INSERT into Inventory VALUES(2001,1099,6,0,0,"N") 26> INSERT into Inventory VALUES(2002,1099,2,0,3,"N") 27> INSERT into Inventory VALUES(2003,1099,7,0,0,"N") 28> 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> 2> SELECT SUM(CD.AverageCost*Inventory.QOH), 3> MAX(CD.AverageCost*Inventory.QOH), 4> MIN(CD.AverageCost*Inventory.QOH) 5> FROM CD,Inventory 6> WHERE CD.CD_ID = Inventory.CD_ID 7> GO
--------------------- ---------------------
1032.1400 131.7800 5.9800
(1 rows affected) 1> 2> 3> drop table Inventory; 4> drop table CD; 5> GO</source>
