Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 273
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 273 The following sections look at the functions by category: First the statistical aggregate functions, then the data management aggregate functions. Statistical Aggregate Functions Statistical aggregate functions perform various operations across their sets. The COUNT() function returns an integer representing the number of rows in the table. The COUNT_BIG() function does the same thing, but it returns a number of type bigint instead. The AVG() function returns the average value for a given column. It requires a column name, and optionally you can use DISTINCT to get an average for just the distinct values in the table. Here s an example that determines the average size of an order from the sample Sales table: SELECT AVG(QtyPurchased) FROM Sales This returns a value of 3, which is the sum of the QtyPurchased (4 + 3 + 1 + 1 + 1 + 10 + 5 + 6, which is 31), divided by the number of records in the table (8). If you divide those two together and truncate the decimal places (because the example deals with integers), you get 3. If you execute this query: SELECT AVG(DISTINCT QtyPurchased) FROM Sales You get the value 4, which is the distinct QtyPurchased values (4 + 3 + 1 + 10 + 5 + 6, which is 29) divided by the number of distinct values (6). Truncate the decimal places and you get 4. MAX() and MIN() are very simple. They return the maximum or the minimum value in the set. Here s an example: SELECT MIN(QtyPurchased), MAX(QtyPurchased) FROM Sales This returns a rowset with one record and two columns: 1 and 10. The SUM function is nearly as simple, it just returns the sum of all the values in the group: SELECT SUM(QtyPurchased), SUM(DISTINCT QtyPurchased) FROM .Sales As you can see, this is another one of the functions that can use the DISTINCT keyword. This returns 31 and 29. Finally, dust off your old statistics blog, because the next two sets of functions are a bit advanced. The VAR() and VARP() functions calculate the variance of
For high quality website hosting services please check tomcat web hosting website.