The TSQL COUNT function is used to count the number
of rows returned in a SELECT statement.
The syntax for the TSQL COUNT function is:
SELECT COUNT (expression)
FROM tables
WHERE conditions;
Only includes NOT NULL Values
Not everyone realizes this, but the TSQL COUNT function will only
include the records in the count where the value of expression in COUNT (expression)
is NOT NULL. When expression contains a NULL value, it is not included in the
COUNT calculations.
Let's look at a TSQL COUNT Function example that demonstrates how NULL
values are evaluated by the COUNT function.
For example, if you have the following table called Customers:
Customer_id
|
Customer_name
|
state
|
1
|
RAY
|
CA
|
2
|
Rohan
|
|
3
|
Thomas
|
And if you ran the following TSQL SELECT statement that uses the TSQL
COUNT functions:
SELECT COUNT(State)
FROM Customers;
This TSQL COUNT example will return 3 since all customer_id values in the query's result set
are NOT NULL.
However, if you ran the next TSQL SELECT statement that uses the TSQL
COUNT functions:
SELECT COUNT(state)
FROM Customers;
This TSQL COUNT example will only return 1, since only one state
value in the query's result set is NOT NULL. That would be the first row where
the state = 'CA'. It is the only row that is included in the COUNT function
calculation.
With Single expression
The simplest way to use the TSQL COUNT function would be to return a
single field that returns the COUNT of something. For example, you might wish
to know how many employees have a salary that is above $25,000 / year.
SELECT COUNT(*) AS "Number
of employees"
FROM employees
WHERE salary > 25000;
In this TSQL COUNT function example, we've aliased the COUNT(*) expression as
"Number of employees". As a result, "Number of employees"
will display as the field name when the result set is returned.
Using TSQL
DISTINCT Clause
You can use the TSQL DISTINCT clause within the TSQL COUNT function. For example, the TSQL statement below returns
the number of unique departments where at least one employee makes over $25,000
/ year.
SELECT COUNT(DISTINCT
department) AS "Unique departments"
FROM employees
WHERE salary > 25000;
Again, the COUNT(DISTINCT department) field is aliased as
"Unique departments". This is the field name that will display in the
result set.
Using TSQL GROUP
BY Clause
In some cases, you will be required to use the TSQL GROUP BY clause with the TSQL COUNT function. For example, you
could use the TSQL COUNT function to return the name of the department and the
number of employees (in the associated department) that make over $25,000 /
year.
SELECT department, COUNT(*)
AS "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;
Because you have listed one column in your TSQL SELECT statement that is not encapsulated in the TSQL COUNT
function, you must use the TSQL GROUP BY clause. The department field must, therefore, be listed
in the GROUP BY section.
No comments:
Post a Comment