COUNT() Function
Learn how to count rows and values in SQL Server using the COUNT() aggregate function.
What is COUNT()?
The COUNT() function is an aggregate function in SQL Server used to count rows or values from a table.
It is commonly used when we want to know how many records are present in a table or how many records satisfy a particular condition.
COUNT() tells us how many records are available.
COUNT() Syntax
SELECT COUNT(column_name)
FROM table_name;
To count all rows, we commonly use:
SELECT COUNT(*)
FROM table_name;
Example Table
Consider the following Employees table:
| EmployeeId | EmployeeName | Department | Salary |
|---|---|---|---|
| 1 | Rahul | IT | 50000 |
| 2 | Priya | HR | 40000 |
| 3 | Amit | IT | 60000 |
| 4 | Neha | HR | 45000 |
| 5 | Rohit | Sales | 35000 |
COUNT(*)
COUNT(*) counts all rows in the table.
SELECT COUNT(*) AS TotalEmployees
FROM Employees;
Output
| TotalEmployees |
|---|
| 5 |
The result is 5 because the Employees table contains five rows.
COUNT(ColumnName)
We can also specify a particular column inside COUNT(). In this case, SQL Server counts the non-NULL values in that column.
SELECT COUNT(Salary) AS SalaryCount
FROM Employees;
If all Salary values are available, the result will be:
SalaryCount
-----------
5
COUNT() with WHERE
COUNT() can be combined with WHERE to count only the records that satisfy a condition.
For example, count employees whose salary is greater than 40000:
SELECT COUNT(*) AS EmployeeCount
FROM Employees
WHERE Salary > 40000;
Output
EmployeeCount
-------------
3
COUNT() with GROUP BY
COUNT() can be used with GROUP BY to count records separately for each group.
SELECT Department,
COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
Output
| Department | EmployeeCount |
|---|---|
| HR | 2 |
| IT | 2 |
| Sales | 1 |
COUNT(DISTINCT)
COUNT(DISTINCT column) is used to count unique non-NULL values.
For example, to count the number of different departments:
SELECT COUNT(DISTINCT Department)
AS TotalDepartments
FROM Employees;
Output
TotalDepartments
----------------
3
The three unique departments are IT, HR and Sales.
COUNT(*) vs COUNT(Column)
| COUNT(*) | COUNT(Column) |
|---|---|
| Counts all rows. | Counts non-NULL values. |
| NULL values do not affect row count. | NULL values are not counted. |
| Useful for total records. | Useful for counting available values. |
Important Points
- COUNT() is an aggregate function.
- COUNT(*) counts all rows.
- COUNT(column) counts non-NULL values.
- COUNT() can be used with WHERE.
- COUNT() can be used with GROUP BY.
- COUNT(DISTINCT column) counts unique values.
Summary
COUNT() is used to count rows or non-NULL values in SQL Server. COUNT(*) counts all rows, while COUNT(column) counts only non-NULL values. COUNT() can also be combined with WHERE, GROUP BY and DISTINCT to perform different types of counting operations.