COUNT (Transact-SQL)
Returns the number of items in a group.
Syntax
COUNT ( { [ALL] expression | * } )Description
COUNT is an aggregate function that takes a set of values and returns the number of values in that set. COUNT can count the number of rows, or the number of non-NULL values in a column, or the number of distinct values in a column.
COUNT supports two basic forms:
COUNT(expression): Counts the number of rows where the specifiedexpressionis notNULL.COUNT(*): Counts all rows in the specified table or view, including rows withNULLvalues in any column.
Arguments
| Argument | Description |
|---|---|
expression |
An expression of any data type. COUNT(expression) counts the number of rows for which expression evaluates to a non-NULL value. |
* |
Specifies that all rows should be counted. COUNT(*) returns the total number of rows in the specified table. |
Return Value
Returns an integer value.
Examples
Example 1: Counting all rows in a table
This example counts the total number of rows in the Sales.Customer table.
SELECT COUNT(*) AS TotalCustomers
FROM Sales.Customer;Output:
TotalCustomers
--------------
19972Example 2: Counting non-NULL values in a specific column
This example counts the number of customers who have an email address specified.
SELECT COUNT(EmailAddress) AS CustomersWithEmail
FROM Sales.Customer;Output:
CustomersWithEmail
------------------
19972Example 3: Counting distinct values
This example counts the number of distinct cities where customers are located.
SELECT COUNT(DISTINCT City) AS DistinctCities
FROM Sales.Customer;Output:
DistinctCities
--------------
168Example 4: Counting within groups
This example counts the number of customers in each country/region.
SELECT CountryRegionCode, COUNT(*) AS NumberOfCustomers
FROM Sales.Customer
GROUP BY CountryRegionCode
ORDER BY NumberOfCustomers DESC;Output:
CountryRegionCode NumberOfCustomers
----------------- -----------------
US 12734
GB 2072
CA 1571
AU 1043
DE 848
... (truncated for brevity)