///////Page 1 starts////////////////////////////////////
How to Use Aggregate Functions in a Select List
//
Aggregate functions (such as SUM, AVG, COUNT, COUNT(*), MAX, and MIN) generate summary values in query result sets. An aggregate function (with the exception of COUNT(*)) processes all the selected values in a single column to produce a single result value. Aggregate functions can be applied to all rows in a table, to a subset of the table specified by a WHERE clause, or to one or more groups of rows in the table. When an aggregate function is applied, a single value is generated from each set of rows.
This example calculates the sum of year-to-date sales for all books in the titles table:
USE pubs
SELECT SUM(ytd_sales)
FROM titles
Here is the result set:
-------------
97446
(1 row(s) affected)
With this query, you can find the average price of all books if prices were doubled:
USE pubs
SELECT avg(price * 2)
FROM titles
Here is the result set:
------------
47.88
(1 row(s) affected)
The table shows the syntax of the aggregate functions and their results (expression is almost always a column name).
Aggregate function Result
SUM([ALL | DISTINCT] expression) Total of the values in the numeric expression
AVG([ALL | DISTINCT] expression) Average of the values in the numeric expression
COUNT([ALL | DISTINCT] expression) Number of values in the expression
COUNT(*) Number of selected rows
MAX(expression) Highest value in the expression
MIN(expression) Lowest value in the expression
SUM, AVG, COUNT, MAX, and MIN ignore null values; COUNT(*) does not.
The optional keyword DISTINCT can be used with SUM, AVG, and COUNT to eliminate duplicate values before an aggregate function is applied (the default is ALL).
SUM and AVG can be used only with numeric columns, for example int, smallint, tinyint, decimal, numeric, float, real, money, and smallmoney data types. MIN and MAX cannot be used with bit data types. Aggregate functions other than COUNT(*) cannot be used with text and image data types.
With these exceptions, aggregate functions can be used with any type of column. For example, in a character data type column, use MIN (minimum) to find the lowest value, the one closest to the beginning of the alphabet:
USE pubs
SELECT MIN(au_lname)
FROM authors
Here is the result set:
--------------------
Bennet
(1 row(s) affected)
The result type returned by an aggregate function may have a larger precision than the inputs so that the result type is large enough to hold the aggregated result value. For example, the SUM or AVG functions return an int value when the data type of the inputs is smallint or tinyint. For more information about the data type returned by an aggregate function, see the topic for the function in the Transact-SQL Reference.
--------------------------------------------------------------------------------
Important The output for statements involving MIN or MAX on char or varchar columns depends on the sort order chosen during installation. For information about the effects of different sort orders, see Sort Order.
--------------------------------------------------------------------------------
When aggregate functions are used in a select list, the select list can contain only:
-Aggregate functions
-Grouping columns from a GROUP BY clause
-An expression that returns the same value for every row in the result set, such as a constant
For more information about generating aggregate values for result sets containing multiple rows, see Grouping Rows with GROUP BY.
Aggregate functions cannot be used in a WHERE clause. However, a SELECT statement with aggregate functions in its select list often includes a WHERE clause that restricts the rows to which the aggregate function is applied. If a SELECT statement includes a WHERE clause (but not a GROUP BY clause), an aggregate function produces a single value for the subset of rows specified by the WHERE clause. This is true whether it is operating on all rows in a table or on a subset of rows defined by a WHERE clause. Such a function is called a scalar aggregate.
This query returns the average advance and the sum of year-to-date sales for business books only:
USE pubs
SELECT AVG(advance), SUM(ytd_sales)
FROM titles
WHERE type = 'business'
Here is the result set:
--------- -------
6,281.25 30788
(1 row(s) affected)
You can use more than one aggregate function in the same select list and produce more than one scalar aggregate in a single SELECT statement.
See Also
Aggregate Functions SELECT
//////////////////////////////////////////////
How to Use COUNT(*) in SQL
//
COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. It counts the total number of rows that meet the qualifications of the query. COUNT(*) returns the number of rows that match the search conditions specified in the query without eliminating duplicates. It counts each row separately, including rows that contain null values. This query finds the total number of books in titles:
USE pubs
SELECT COUNT(*)
FROM titles
Here is the result set:
-----------
18
(1 row(s) affected)
COUNT(*) can be combined with other aggregate functions. This query shows COUNT(*) combined with an AVG function in which both aggregate functions aggregate data only from the rows that satisfy the WHERE clause search condition:
USE pubs
SELECT COUNT(*), AVG(price)
FROM titles
WHERE advance > $1000
Here is the result set:
----------- --------------------------
15 14.42
(1 row(s) affected)
//////////////////////////////////////
How to Use DISTINCT with Aggregate Functions
//
The DISTINCT keyword is optional with SUM, AVG, and COUNT. When DISTINCT is used, duplicate values are eliminated before the sum, average, or count is calculated.
If you use DISTINCT, the expression must consist of a column name only. It cannot include an arithmetic expression.
This query returns the average prices of business books (without duplicate values):
USE pubs
SELECT AVG(DISTINCT price)
FROM titles
WHERE type = 'business'
Here is the result set:
------------
14.63
(1 row(s) affected)
Without DISTINCT, the AVG function finds the average price of all business titles:
USE pubs
SELECT AVG(price)
FROM titles
WHERE type = 'business'
Here is the result set:
------------
15.97
(1 row(s) affected)
/////////////////////////////////////
How to Use Null Values and Aggregate Functions
Null values in a column are ignored while an aggregate function is operating.
For example, the count of advances in the titles table is not the same as the count of title names, because null values in the advance column are not counted.
USE pubs
SELECT COUNT(advance)
FROM titles
Here is the result set:
------------
16
(1 row(s) affected)
USE pubs
SELECT COUNT(title)
FROM titles
Here is the result set:
------------
18
(1 row(s) affected)
If no rows meet the condition(s) specified in the WHERE clause, COUNT returns a value of zero. The other functions all return NULL. Here are two examples:
USE pubs
SELECT COUNT(DISTINCT title)
FROM titles
WHERE type = 'poetry'
Here is the result set:
------------
0
(1 row(s) affected)
USE pubs
SELECT AVG(advance)
FROM titles
WHERE type = 'poetry'
Here is the result set:
------------
(null)
(1 row(s) affected)
COUNT(*), however, counts each row, even if all column values are NULL.
Here is the result set:
///////////////////////////
Breakdown of your Risk Level
Related Content
