SQL COUNT Tutorial

Example Table

We will use the following table named employees for our examples:

                CREATE TABLE employees (
                    employee_id INT PRIMARY KEY,
                    employee_name VARCHAR(50),
                    department VARCHAR(50),
                    salary DECIMAL(10, 2)
                );
        
                INSERT INTO employees (employee_id, employee_name, department, salary) VALUES
                (1, 'John Doe', 'HR', 50000.00),
                (2, 'Jane Smith', 'Finance', 60000.00),
                (3, 'Sam Brown', 'IT', 70000.00),
                (4, 'Nancy White', 'HR', 55000.00),
                (5, 'Mike Green', 'Finance', 65000.00);
                

employee_id	employee_name	department	salary
1	John Doe	HR	50000.00
2	Jane Smith	Finance	60000.00
3	Sam Brown	IT	70000.00
4	Nancy White	HR	55000.00
5	Mike Green	Finance	65000.00

Using SQL COUNT

The SQL COUNT function returns the number of rows that match a specified criterion.

Counting All Rows

To count all rows in a table, use COUNT(*):

                SELECT COUNT(*) AS total_employees
                FROM employees;
                

Result:

total_employees	5

Counting Rows with a Condition

To count rows that meet a specific condition, use COUNT(*) with a WHERE clause:

                SELECT COUNT(*) AS hr_employees
                FROM employees
                WHERE department = 'HR';
                

Result:

hr_employees	2

Counting Non-NULL Values

To count the number of non-NULL values in a specific column, use COUNT(column_name):

                SELECT COUNT(department) AS non_null_departments
                FROM employees;
                

Result:

non_null_departments	5

Counting Distinct Values

To count the number of distinct values in a column, use COUNT(DISTINCT column_name):

                SELECT COUNT(DISTINCT department) AS unique_departments
                FROM employees;
                

Result:

unique_departments	3