SQL — Count

COUNT() Function

COUNT() returns the number of rows that match a condition. It's one of the most commonly used aggregate functions.

COUNT(*) — Count All Rows

SELECT COUNT(*) AS total_employees
FROM employees;

This counts every row, including those with NULL values.

COUNT(column) — Count Non-NULL Values

SELECT COUNT(phone_number) AS employees_with_phone
FROM employees;

Rows where phone_number is NULL are excluded from the count.

COUNT(DISTINCT) — Count Unique Values

SELECT COUNT(DISTINCT department_id) AS unique_departments
FROM employees;

If multiple employees share the same department_id, it's only counted once.

COUNT with WHERE

SELECT COUNT(*) AS high_earners
FROM employees
WHERE salary > 80000;

COUNT with GROUP BY

This is the most common real-world pattern — counting rows per category:

SELECT
  department_id,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
ORDER BY employee_count DESC;

COUNT with HAVING

Filter groups based on their count:

SELECT
  department_id,
  COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

COUNT vs SUM

COUNT tallies rows; SUM totals values:

-- Number of sales transactions
SELECT COUNT(*) AS total_transactions FROM sales;

-- Total revenue from those transactions
SELECT SUM(amount) AS total_revenue FROM sales;

COUNT with CASE

Conditional counting:

SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN salary > 80000 THEN 1 END) AS high_earners,
  COUNT(CASE WHEN salary <= 80000 THEN 1 END) AS others
FROM employees;

NULL Behavior

CREATE TABLE demo (val INT);
INSERT INTO demo VALUES (1), (2), (NULL), (NULL);

SELECT COUNT(*) FROM demo;        -- 4 (counts all rows)
SELECT COUNT(val) FROM demo;      -- 2 (skips NULLs)
SELECT COUNT(DISTINCT val) FROM demo; -- 2 (1 and 2)

Practice

1. Count the total number of products in a products table
2. Count how many products are in each category
3. Find categories with more than 10 products
4. Count distinct customers who placed orders in the last 30 days