COUNT

-- Returns the total number of rows in the table (includes NULL).
SELECT COUNT(*) FROM table_name;

-- Returns the number of rows where the specified column is not NULL.
SELECT COUNT(column_name) FROM table_name;

-- Returns the number of distinct (non-duplicate) values in the specified column.
SELECT COUNT(DISTINCT column_name) FROM table_name;

-- Returns the number of rows that match the condition.
SELECT COUNT(*) FROM table_name WHERE condition;

-- Returns the total number of employees.
SELECT COUNT(*) AS employee_count FROM employees;

-- Returns the number of employees who have a bonus set (excludes NULL).
SELECT COUNT(bonus) AS bonus_eligible_count FROM employees;

-- Returns the number of distinct departments.
SELECT COUNT(DISTINCT department) AS department_count FROM employees;

-- Returns the number of employees in the Sales department.
SELECT COUNT(*) AS sales_count FROM employees WHERE department = 'Sales';

-- Returns the number of employees per department (used with GROUP BY).
SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
ORDER BY headcount DESC;

-- Example result of: SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department ORDER BY headcount DESC;
-- +------------+-----------+
-- | department | headcount |
-- +------------+-----------+
-- | Engineering|         8 |
-- | Sales      |         6 |
-- | HR         |         3 |
-- +------------+-----------+

The COUNT(*), COUNT(column_name), and COUNT(DISTINCT column_name) syntaxes work the same way across all major databases.

SELECT COUNT(*) AS employee_count FROM employees;
SELECT COUNT(DISTINCT department) AS department_count FROM employees;
SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department;

In Oracle, you can use a parallel hint (/*+ PARALLEL */) to speed up COUNT(*) on large tables.

-- Oracle (parallel execution for better performance)
SELECT /*+ PARALLEL(employees, 4) */ COUNT(*) FROM employees;

COUNT is an aggregate function. The key difference between COUNT(*) and COUNT(column_name) is how they handle NULL values. If a column contains NULL values, the two forms can return different results on the same table, so use the appropriate form with care.

When using COUNT(*) > 0 to check whether rows exist, an EXISTS subquery is faster in MySQL because it stops as soon as a matching row is found. For existence checks on large tables, consider using EXISTS instead.

For per-group aggregation, see GROUP BY / HAVING. For totals and averages, see SUM / AVG.