The COUNT() aggregate function counts and returns the number of rows when using SELECT.

Structure

SELECT COUNT(EXPRESSION)
FROM TABLE
WHERE [CONSTRAINTS]

Example

Let’s say we want to count the number of users in the Engineering department that have an active status. When know that the Engineering department’s ID is 2. All the information that we need is in the users table. This is how the SQL query would be written:

SELECT COUNT(u.id) as number_of_users from users u
WHERE dept_id = 2 and active = 1;