SQL Aggregate Functions

SQL COUNT Function: Count Rows and Values (All Databases)

SQL COUNT function to count rows, distinct values, and conditional counts. Covers COUNT(*), COUNT(column), COUNT(DISTINCT), and conditional counting.

Mar 12, 20265 min read

Overview

COUNT is the most used aggregate function. It counts rows, non-NULL values, or distinct values — but the differences matter.

All Databases

-- COUNT(*) — counts ALL rows (including NULLs):
SELECT COUNT(*) FROM users;  -- total rows

-- COUNT(column) — counts non-NULL values only:
SELECT COUNT(email) FROM users;  -- rows where email is not NULL

-- COUNT(DISTINCT column) — counts unique non-NULL values:
SELECT COUNT(DISTINCT country) FROM users;  -- unique countries

-- COUNT with GROUP BY:
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

-- Conditional counting:
SELECT
  COUNT(*) AS total,
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive
FROM users;

-- FILTER syntax (PostgreSQL):
-- SELECT COUNT(*) FILTER (WHERE status = 'active') FROM users;

Skip the Syntax Lookup

Instead of memorizing COUNT syntax for each database, describe what you need in plain English and let AI2SQL generate the correct query.

Try AI2SQL Free

No credit card required

Frequently Asked Questions

What is the difference between COUNT(*) and COUNT(column)?

COUNT(*) counts all rows including those with NULLs. COUNT(column) counts only rows where that column is not NULL. COUNT(DISTINCT column) counts unique non-NULL values.

How do I count rows with a condition?

Use COUNT(CASE WHEN condition THEN 1 END) or in PostgreSQL, COUNT(*) FILTER (WHERE condition).

Can AI2SQL generate COUNT queries?

Yes. Say 'count users by country' or 'count active vs inactive users' and AI2SQL generates the correct COUNT with grouping.