SQL Server/T-SQL Tutorial/Aggregate Functions/AVG

Материал из SQL эксперт
Перейти к: навигация, поиск

adds the WHERE statement to calculate the average

   <source lang="sql">

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> 2> SELECT AVG(SalesPrice),AVG(AverageCost) from CD WHERE Classif_ID = 1 3> GO


---------------------
             14.9900                5.9373

(1 rows affected) 1> 2> drop table CD; 3> GO</source>


A summary query that uses the COUNT, AVG, and SUM functions

   <source lang="sql">

3> 4> create table Billings ( 5> BankerID INTEGER, 6> BillingNumber INTEGER, 7> BillingDate datetime, 8> BillingTotal INTEGER, 9> TermsID INTEGER, 10> BillingDueDate datetime , 11> PaymentTotal INTEGER, 12> CreditTotal INTEGER 13> 14> ); 15> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> SELECT "After 1/1/2002" AS SelectionDate, COUNT(*) AS NumberOfBillings, 4> AVG(BillingTotal) AS AverageBillingAmount, 5> SUM(BillingTotal) AS TotalBillingAmount 6> FROM Billings 7> WHERE BillingDate > "2002-01-01" 8> GO SelectionDate NumberOfBillings AverageBillingAmount TotalBillingAmount


---------------- -------------------- ------------------

After 1/1/2002 6 165 990 (1 rows affected) 1> 2> drop table Billings; 3> GO</source>


AVG calculates the average for selected records of numeric data in a column or the average for DISTINCT (unique) values of the selected records.

   <source lang="sql">

All NULL values are ignored in the computation for the total and the record count. computes the average on the rows selected by the query, or the DISTINCT rows. The return datatype is the same datatype as the column being averaged. Only the numeric datatypes can be used with the AVG function.

6> CREATE TABLE sales( 7> stor_id char(4) NOT NULL, 8> ord_num varchar(20) NOT NULL, 9> ord_date datetime NOT NULL, 10> qty smallint NOT NULL, 11> payterms varchar(12) NOT NULL, 12> title_id varchar(80) 13> ) 14> 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 avg(qty) from sales; 4> GO


        25

(1 rows affected) 1> 2> drop table sales; 3> 4> GO 1></source>


CAST(AVG(dec1) AS dec(5,2))

   <source lang="sql">

6> CREATE TABLE T ( 7> int1 int, 8> bit1 bit, 9> varchar1 varchar(3), 10> dec1 dec(5,2), 11> cmp1 AS (int1 + bit1) 12> ) 13> GO 1> 2> INSERT T (int1, bit1) VALUES (1, 0) 3> INSERT T (int1, varchar1) VALUES (2, "abc") 4> INSERT T (int1, dec1) VALUES (3, 5.25) 5> INSERT T (bit1, dec1) VALUES (1, 9.75) 6> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) 1> SELECT CAST(AVG(dec1) AS dec(5,2)) "Avg of dec1" 2> FROM T 3> WHERE dec1 IS NOT NULL 4> GO Avg of dec1


      7.50

(1 rows affected) 1> drop table t; 2> GO 1> 2></source>


Selecting titles that sell more than the average.

   <source lang="sql">

3> 4> 5> CREATE TABLE titles( 6> title_id varchar(20), 7> title varchar(80) NOT NULL, 8> type char(12) NOT NULL, 9> pub_id char(4) NULL, 10> price money NULL, 11> advance money NULL, 12> royalty int NULL, 13> ytd_sales int NULL, 14> notes varchar(200) NULL, 15> pubdate datetime NOT NULL 16> ) 17> GO 1> 2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94") 3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91") 4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91") 5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91") 6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91") 7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91") 8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91") 9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91") 10> 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> 2> CREATE TABLE sales( 3> stor_id char(4) NOT NULL, 4> ord_num varchar(20) NOT NULL, 5> ord_date datetime NOT NULL, 6> qty smallint NOT NULL, 7> payterms varchar(12) NOT NULL, 8> title_id varchar(80) 9> ) 10> 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> SELECT titles.title, titles.price 3> FROM titles JOIN sales ON sales.title_id = titles.title_id 4> WHERE sales.qty > 5> (SELECT AVG(qty) 6> FROM sales) 7> GO title price


---------------------

Secrets 20.0000 (1 rows affected) 1> 2> drop table sales; 3> drop table titles; 4> GO</source>


Using DISTINCT in Aggregate Functions

   <source lang="sql">

3> 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> ); 13> 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) 4> FROM employee; 5> GO


                            5505.444444

(1 rows affected) 1> 2> 3> 4> 5> drop table employee; 6> GO</source>