T-SQL COUNT() Function
The T-SQL (Transact-SQL) COUNT() function returns the count of an expression.
Syntax
The syntax for using COUNT() function is given below:
SELECT COUNT(column_name) FROM table_name WHERE condition(s);
The T-SQL (Transact-SQL) GROUP BY clause is used to arrange result table into identical groups when one or more columns are used. Please note that it is must to include those column names in a GROUP BY clause which are not encapsulated within the COUNT() function. See the syntax below:
SELECT column1, column2, ... COUNT(column_name) FROM table_name WHERE condition(s) GROUP BY column1, column2, ...;
Parameters
column1, column2, ... |
Specify the column names that are not encapsulated within the COUNT() function. It must be included in the GROUP BY clause. |
column_name |
Specify the column or expression whose non-null values need to be counted. |
table_name |
Specify the table name from where the records need to retrieved. |
WHERE condition(s) |
Optional. Specify the condition(s). Records are selected based upon specified condition(s). |
Return Value
Returns the count of non-null value of a given expression.
Example - With Single Column
Consider a database table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
To get the count of employees whose age is greater than 25, the following query can be used:
SELECT COUNT(Name) AS EmployeeGT25 FROM Employee WHERE Age > 25;
This will produce the result as shown below:
EmployeeGT25 |
---|
4 |
Example - Using DISTINCT
The DISTICT clause can be used with COUNT() function. For example - To get the count of distinct (unique) age of the employees, the following code can be used:
SELECT COUNT(DISTINCT Age) AS DistinctAge FROM Employee;
This will produce the result as shown below:
DistinctAge |
---|
5 |
Example - Using GROUP BY
To get the count of employees group by their age, the following query can be used:
SELECT Age, COUNT(Age) AS NumberOfEmployee FROM Employee GROUP BY Age;
This result of the above code will be:
Age | NumberOfEmployee |
---|---|
24 | 1 |
25 | 1 |
27 | 1 |
28 | 2 |
30 | 1 |
❮ T-SQL Functions