SQL Server COUNT() Function

In SQL Server, the COUNT() function is an aggregate function used to count the number of rows in a specified column or expression.

Syntax

The basic syntax for the COUNT() function is as follows:

COUNT(expression)

where expression is the column or expression to count rows. If expression is an asterisk *, all rows will be counted.

Usage

The COUNT() function is commonly used to count the number of rows in a table. For example, COUNT(*) can be used to count the total number of rows in a table. Additionally, the COUNT() function can be used to count the number of non-null values in a specified column.

Examples

Here are two examples of using the COUNT() function:

Example 1

Suppose there is a student scores table as follows:

id	name	score
1	Tom	85
2	Jack	90
3	Alice	80
4	Bob

The following SQL statement can be used to count the total number of students and the number of students with non-null scores:

SELECT
    COUNT(*) AS total_students,
    COUNT(score) AS non_null_scores
FROM student_scores;

The result is:

total_students	non_null_scores
4	3

Example 2

Suppose there is an orders table as follows:

order_id	customer_id	order_total
1	1001	50
2	1002	75
3	1001	100
4	1003

The following SQL statement can be used to count the total number of orders and the number of orders with non-null order totals:

SELECT
    COUNT(*) AS total_orders,
    COUNT(order_total) AS non_null_totals
FROM orders;