Skip to main content
Back to Documentation
DocsAggregationsCOUNT

COUNT

Aggregations

Concept

COUNT is the most frequently used aggregate function. COUNT(*) counts every row in the group, including rows with NULL values. COUNT(column) counts only non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. Understanding these three variants is essential for accurate reporting.

COUNT is often combined with GROUP BY to produce per-group tallies — for example, the number of orders per customer or the number of products per category. Without GROUP BY, COUNT collapses the entire table into a single summary row.

Because COUNT(*) includes NULLs and COUNT(column) excludes them, the two can return different numbers for the same table. This difference is not a bug; it is a tool. When you specifically want to know how many rows have a value in a nullable column, COUNT(column) is the right choice.

Syntax

-- Count all rows
SELECT COUNT(*) FROM orders;

-- Count non-NULL values
SELECT COUNT(phone) FROM customers;

-- Count distinct values
SELECT COUNT(DISTINCT category) FROM products;

-- Count per group
SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- How many orders has each customer placed?
SELECT
  c.customer_id,
  c.first_name || ' ' || c.last_name AS customer,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY order_count DESC;

-- Monthly order volume
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  COUNT(DISTINCT customer_id) AS unique_customers
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;

Common Pitfalls & Tips

  • 1COUNT(*) vs COUNT(column): they are NOT interchangeable. COUNT(*) counts all rows; COUNT(column) ignores NULLs. Pick the one that matches your intent.
  • 2When using LEFT JOIN with COUNT, count the right-side column (e.g., COUNT(o.order_id)) not COUNT(*) — otherwise customers with zero orders will show a count of 1.
  • 3COUNT(DISTINCT ...) can be slow on very large tables. Approximate alternatives like HyperLogLog are available in some databases.
Practice COUNT queries