The COUNT function in SQL Server is used to return the number of rows in a result set.
It can be used in a SELECT statement to count the total number of rows that match a certain condition, or it can be used on its own to return the total number of rows in a table.
The basic syntax for the COUNT function is as follows:
Syntax
SELECT COUNT(column_name) FROM table_name;
SELECT COUNT(*) FROM table_name;
The asterisk (*) after the COUNT keyword tells the function to count all rows, regardless of the values in the columns.
If you specify a column name instead, the function will only count the rows where the column is not NULL.
Examples
Sales table:
| ID | PRICE | NAME |
|---|---|---|
| 1 | 200 | A |
| 2 | 500 | B |
| 3 | 900 | C |
| 4 | 500 | D |
Count rows
SELECT COUNT(id)
FROM Sales WHERE price=500;
This would return the total number of rows in the Sales table with price 500.
Count total rows
SELECT COUNT(*) FROM Sales;
This would return the total number of rows in the Sales table.
Additionally, COUNT function can be used in aggregate function with GROUP BY clause to count the number of row for each group.