? MySQL COUNT() Function

⚡ Quick Overview

The COUNT() function is an aggregate function in MySQL used to return the number of rows that match a specified criterion. It is commonly used in reporting to calculate totals, such as the number of orders, active users, or products in a category.

? Key Concepts

Aggregate It summarizes multiple rows into a single value.
NULL Handling COUNT(*) includes NULLs, while COUNT(column) ignores them.
Grouping Often used with GROUP BY to count items per category.

? Syntax / Theory

There are three main ways to use the function:

COUNT(*): Returns the total number of rows in a table (including NULLs).
COUNT(column_name): Returns the number of rows where the column is NOT NULL.
COUNT(DISTINCT column_name): Returns the number of unique non-null values.

? Code Examples

1. Basic Usage

? View Code Example

-- 1. Count ALL rows in the 'employees' table (fastest method)
SELECT COUNT(*) FROM employees;

-- 2. Count only employees who have a manager (ignores NULL manager_id)
SELECT COUNT(manager_id) FROM employees;

-- 3. Count unique job titles available in the company
SELECT COUNT(DISTINCT job_title) FROM employees;

2. Using Group By

Counting records per category is one of the most common use cases.

? View Code Example

-- Count the number of employees in each department
SELECT 
    department_name, 
    COUNT(*) as employee_count 
FROM employees 
GROUP BY department_name;

-- Filter groups: Show only departments with more than 5 employees
SELECT 
    department_name, 
    COUNT(*) as employee_count 
FROM employees 
GROUP BY department_name
HAVING COUNT(*) > 5;

? Live Output Explanation

Scenario: You have an employees table with 10 rows. 2 employees have no manager (NULL).

SELECT COUNT(*) → Returns 10 (Total rows).
SELECT COUNT(manager_id) → Returns 8 (Only non-null managers).
SELECT COUNT(DISTINCT job_title) → Returns the number of unique roles (e.g., 4).

? Tips & Best Practices

Performance: COUNT(*) is generally optimized and faster than COUNT(column) if you just need the total row count.
Zero vs NULL: COUNT() returns 0 if no rows match, not NULL.
Aliases: Always use an alias (e.g., AS total_orders) to make the result readable in your application code.

? Try It Yourself

Write a query to count the total number of users in your users table.
Count how many users have a verified email address (assuming an email_verified_at column exists).
Find the number of orders placed by each customer using GROUP BY customer_id.