CodeCapsule

SQL COUNT function

by

Here’s a clean and practical guide to the SQL COUNT() function, including behavior with NULLs, best practices, and real-world examples.

✅ What COUNT() Does

COUNT() returns the number of rows that match a condition.

✅ Types of COUNT

1. COUNT(*) — Count all rows

Counts every row, including those with NULL values.

SELECT COUNT(*) AS total_rows
FROM users;

2. COUNT(column) — Count non-NULL values

Ignores NULLs in the specified column.

SELECT COUNT(email) AS users_with_email
FROM users;

If email is NULL for some users, those rows are not counted.

3. COUNT(DISTINCT column) — Count unique non-NULL values

Useful for determining how many unique values exist.

SELECT COUNT(DISTINCT country) AS unique_countries
FROM customers;

🔥 COUNT with WHERE (filter first)

SELECT COUNT(*) AS active_users
FROM users
WHERE status = 'active';

🔥 COUNT with GROUP BY (per category)

SELECT department, COUNT(*) AS employees_in_dept
FROM employees
GROUP BY department;

🧠 Important Behavior Notes

✔ COUNT(*) counts all rows
✔ COUNT(column) ignores NULLs
✔ COUNT(DISTINCT ...) ignores NULLs and removes duplicates
✔ Use COUNT with GROUP BY for per-group counts
✔ Use WHERE to filter before counting

⚡ Real-World Examples

Number of completed orders

SELECT COUNT(*) AS completed_orders
FROM orders
WHERE status = 'completed';

Count users by signup month

SELECT DATE_TRUNC('month', created_at) AS month,
COUNT(*) AS signups
FROM users
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *