Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

COUNT

SQL Server 2000

Returns the number of items in a group.

Syntax

COUNT ( { [ ALL | DISTINCT ] expression ] | * } )

Arguments

ALL

Applies the aggregate function to all values. ALL is the default.

DISTINCT

Specifies that COUNT returns the number of unique nonnull values.

expression

Is an expression of any type except uniqueidentifier, text, image, or ntext. Aggregate functions and subqueries are not permitted.

*

Specifies that all rows should be counted to return the total number of rows in a table. COUNT(*) takes no parameters and cannot be used with DISTINCT. COUNT(*) does not require an expression parameter because, by definition, it does not use information about any particular column. COUNT(*) returns the number of rows in a specified table without eliminating duplicates. It counts each row separately, including rows that contain null values.

Important  Distinct aggregates, for example AVG(DISTINCT column_name), COUNT(DISTINCT column_name), MAX(DISTINCT column_name), MIN(DISTINCT column_name), and SUM(DISTINCT column_name), are not supported when using CUBE or ROLLUP. If used, Microsoft® SQL Server™ returns an error message and cancels the query.

Return Types

int

Remarks

COUNT(*) returns the number of items in a group, including NULL values and duplicates.

COUNT(ALL expression) evaluates expression for each row in a group and returns the number of nonnull values.

COUNT(DISTINCT expression) evaluates expression for each row in a group and returns the number of unique, nonnull values.

Examples
A. Use COUNT and DISTINCT

This example finds the number of different cities in which authors live.

USE pubs
GO
SELECT COUNT(DISTINCT city)
FROM authors
GO

Here is the result set:

----------- 
16          

(1 row(s) affected)
B. Use COUNT(*)

This example finds the total number of books and titles.

USE pubs
GO
SELECT COUNT(*)
FROM titles
GO

Here is the result set:

----------- 
18          

(1 row(s) affected)
C. Use COUNT(*) with other aggregates

The example shows that COUNT(*) can be combined with other aggregate functions in the select list.

USE pubs
GO
SELECT COUNT(*), AVG(price)
FROM titles
WHERE advance > $1000
GO

Here is the result set:

----------- -------------------------- 
15          14.42                      

(1 row(s) affected)

See Also

Aggregate Functions

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft