n n n in C#.NET

Make QR Code ISO/IEC18004 in C#.NET n n n

Aggregate functions allow you to apply calculations on values in a column. Adding the GROUP BY clause allows you to provide aggregate on subsets of the data.

After this lesson, you will be able to:

Describe the purpose of the aggregate functions available. Group aggregate data by using the GROUP BY statement.

Aggregate functions perform calculations on a set of data and return a scalar (single) value. The following aggregate functions are available in SQL Server 2008:

Returns the average of all values in the data set. Returns the checksum of all values in the data set.

COUNT Returns the number of values contained in the data set. COUNT(*) returns the number of rows in the set. When a column is specified, such as COUNT(FaxNo), the value returned reflects the number of rows that contain data in that column. NULL values are ignored. In addition, COUNT DISTINCT returns the number of unique non-NULL values in the data set. COUNT_BIG Works the same as COUNT, but it returns the bigint data type, while COUNT returns only the int data type. GROUPING Returns 1 or 0 and identifies rows as aggregate or detail rows when the GROUP BY statement is used. A value of 1 indicates an aggregate row, while 0 indicates details. MAX

Returns the highest value in the data set for numeric, data, and character-based Returns the lowest value in the data set for numeric, data, and character-based

SUM Returns the total of the values in the data set. You can specify ALL or DISTINCT to produce either the sum of all values or only distinct values in the data set. STDEV

STDEVP Returns the statistical standard deviation for the population of the values in the data set. VAR VARP

Returns the statistical variance of the values in the data set. Returns the statistical variance for the population of the values in the data set.

NULL values are ignored for all the aggregate functions. It is important to understand this so that you can verify the data is being properly interpreted. For example, in a table that maintains test scores, there is a big difference between NULL and 0 when you use the AVG or MIN aggregates. If the test is excused and should not be included in the calculation, the NULL value does not have a negative impact on the accuracy of the calculation, but if the test should be averaged in, the database integrity checks should make sure that 0 is entered rather than NULL. The following sample returns the average, maximum, and minimum list prices of all products in the Production.Product table. Products that are either new and have not been priced or that are not sold to consumers have a list price of 0. To provide more accurate aggregates, these products are removed from the result set:

SELECT AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product WHERE ListPrice <> 0;

Frequently, the GROUP BY clause is included in queries with aggregate functions. When an aggregate function is included in the SELECT clause, all other expressions in the SELECT clause must either be aggregate functions or included in a GROUP BY clause. The GROUP BY clause allows you to define subtotals for the aggregate data. For example, the following command returns the average, minimum, and maximum list prices for products that belong to each product subcategory:

SELECT Production.Product.ProductSubcategoryID , AVG(Listprice) AS 'Average' , MIN(Listprice) AS 'Minimum' , MAX(Listprice) AS 'Maximum' FROM Production.Product WHERE ListPrice <> 0 GROUP BY Product.ProductSubcategoryID;

The result set for the preceding query is shown in Figure 1-6. The top row, where the ProductSubcategoryID is listed as NULL, is the summary row that provides the average, minimum, and maximum list prices of products across all subcategories.