Using Aggregate Functions

Aggregate functions operate on a set of rows to calculate and return a single value.

Using Aggregate Functions It is often necessary to summarize data without actually retrieving it all, and SQL Server provides special functions for this purpose. Using these functions, T-SQL queries are often used to retrieve data for analysis and reporting purposes. Here are some examples of this type of retrieval:  

1. Determining the number of rows in a table (or the number of rows that meet some condition or contain a specific value) 
2. Obtaining the sum of a group of rows in a table 
3. Finding the highest, lowest, and average values in a table column (either for all rows or for specific rows)

Caution about "Avg()":

? Individuals columns only.

Avg() may only be used to determine the average of a specific numeric column, and that column name must be specified as the function parameter. To obtain the average value of multiple columns, you must use multiple Avg() functions.

? NULL Values

Column rows containing NULL values are ignored by the Avg() function.

Note about “Count()”: NULL Values

Column rows with NULL values in them are ignored by the Count() function if a column name is specified, but not if the asterisk (*) is used.

Note about “Max()”, “Min()” and “Sum()”: 

? NULL Values. 

Column rows with NULL values in them are ignored by the Max(), Min() and Sum() functions.

? Using Max() with Nonnumeric Data.

Although Max() is usually used to find the highest numeric or date value, T-SQL allows it to be used to return the highest value in any column, including textual columns. 

When used with textual data, Max() returns the row that would be the last if the data were sorted by that column.

? Using Min() with Nonnumeric Data.

Similar to the Max() function, T-SQL allows Min() to be used to return the lowest value in any columns, including textual columns.

When used with textual data, Min() returns the row that would be first if the data were sorted by that column.