SQL: COUNT FUNCTION

SQL COUNT function with syntax, examples, and practice exercises.

DESCRIPTION

 SQL COUNT function is generally used to count the number of rows returned in a SELECT statement.

SYNTAX

Syntax for SQL COUNT function is:

SELECT COUNT(expression) FROM tables WHERE conditions;

expression can be a numeric field or formula.

ONLY INCLUDES NOT NULL VALUES

SQL COUNT function example that demonstrates how NULL values are evaluated by the COUNT function.
For example, if you have the following table called employee:

employee_id	employee_name	age
1	bob	22
2	julie
3	sweety

if you ran the following SQL SELECT statement that uses the SQL COUNT function:

SELECT COUNT(employee_id) FROM employee;

it return 3 since all employee_id values in the query's result set are NOT NULL.
SQL SELECT statement that uses the SQL COUNT function:

SELECT COUNT(age) FROM employee;

It will only return 1, since only one age value in the query's result set is NOT NULL.

EXAMPLE - SINGLE EXPRESSION

The simplest way to use the SQL COUNT function would be to return a single field that returns the COUNT of something.

For example, return the list of employee  count who is getting more than 20,000

SELECT COUNT(*) AS "Number of employees" FROM employees WHERE salary > 20000;

As a result, "Number of employees" will display as the field name when the result set is returned.

EXAMPLE - GROUP BY CLAUSE

The SQL GROUP BY clause with the SQL COUNT function.

For example, you could use the SQL COUNT function to return the name of the department and the number of employees (in the associated department) that make over 20,000 / year.

SELECT designation, COUNT(*) AS "Number of employees" FROM employees WHERE salary > 20000 GROUP BY designation;