What does it do?

COUNT() is an aggregate function that returns the number of records that matches specified criteria by a select query.

Syntax

The general syntax for COUNT() function is:

COUNT(expression)

Example

We have this departments table

dept_no dept_name
d001 Marketing
d002 Finance
d003 Human Resources
d004 Production
d005 Development
d006 Quality Management
d007 Sales
d008 Research
d009 Customer Service

And we count the total number of departments:

SELECT
	COUNT(dept_no) AS 'Number of departments'
FROM
	departments;

Then it returns:

Number of departments
9

More examples

In practice, COUNT() function is mostly used in a combination with WHERE and GROUP BY clauses.

For example, we have a table employees with all the employees’ information. Let’s count the total number of employees:

SELECT
	COUNT(*) AS 'Total Employees'
FROM
	employees;

And we get the number:

Total Employees
443308

Now count the total number of Senior Staff from the table of all employees:

SELECT
	COUNT(*) AS 'Number of Senior Staff'
FROM
	employees
WHERE
	title = 'Senior Staff';

And it returns:

Number of Senior Staff
92853

Let’s use GROUP BY to count the number of each position in the company:

SELECT
	title,
	COUNT(*) AS 'Number of Senior Staff'
FROM
	employees
GROUP BY
	title;

The results will be:

title Number of Senior Staff
Assistant Engineer 15128
Senior Staff 92853
Engineer 115003
Staff 107391
Technique Leader 15159
Senior Engineer 97750
Manager 24

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage MySQL, SQL Server, PostgreSQL and many other databases simultaneously using an intuitive and powerful graphical interface.

Download TablePlus for Mac.

Not on Mac? Download TablePlus for Windows.

Need a quick edit on the go? Download for iOS

TablePlus in Dark mode