---
title: "SQL COUNT Statement: 10 Examples With Query Results"
description: "The SQL COUNT statement returns a number: COUNT(*) counts rows, COUNT(column) counts non-null values, and COUNT(DISTINCT column) counts unique non-null values."
canonical: "https://www.aifordatabase.com/blog/sql-count-statement-examples/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-22
updated: 2026-11-22
---

# SQL COUNT Statement: 10 Examples With Query Results

**TL;DR:** The SQL COUNT statement returns a number: `COUNT(*)` counts rows, `COUNT(column)` counts non-null values, and `COUNT(DISTINCT column)` counts unique non-null values. Add `GROUP BY` for counts per group, `FILTER` or `CASE` for conditional counts, and a `LEFT JOIN` to a date series when you need days with zero rows. If you would rather ask "how many customers ordered this week?" in plain English, AI for Database writes and runs the COUNT query for you and shows the SQL.

> Want the answer without writing the query? Try AI for Database free at aifordatabase.com ([start here](https://app.aifordatabase.com/signup), sample data included).

The SQL COUNT statement is an aggregate function: `SELECT COUNT(*) FROM orders;` returns the number of rows in `orders`, `SELECT COUNT(coupon_code) FROM orders;` returns how many rows have a coupon (NULLs are skipped), and `SELECT COUNT(DISTINCT customer_id) FROM orders;` returns how many different customers bought. In PostgreSQL every form of `count` returns `bigint`; in SQL Server `COUNT` returns `int` and `COUNT_BIG` returns `bigint` ([Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql), [PostgreSQL docs](https://www.postgresql.org/docs/current/functions-aggregate.html)). Every example below runs on the same two small tables, and each one shows the exact result it returns, so you can check your understanding line by line.

## The sample schema

Two tables: five customers, six orders. One customer has no plan (NULL), two customers have never ordered, and three orders have no coupon.

```
CREATE TABLE customers (id int PRIMARY KEY, name text, plan text, country text);
INSERT INTO customers VALUES
  (1, 'Ana',  'pro',  'IN'),
  (2, 'Ben',  'free', 'US'),
  (3, 'Cara', 'pro',  'US'),
  (4, 'Dev',  NULL,   'IN'),
  (5, 'Eli',  'free', 'UK');

CREATE TABLE orders (id int PRIMARY KEY, customer_id int, created_at date,
                     status text, amount numeric, coupon_code text);
INSERT INTO orders VALUES
  (10, 1, '2026-11-01', 'paid',     40, 'FALL10'),
  (11, 1, '2026-11-01', 'refunded', 25, NULL),
  (12, 2, '2026-11-01', 'paid',     15, NULL),
  (13, 3, '2026-11-03', 'paid',     60, 'FALL10'),
  (14, 3, '2026-11-03', 'paid',     20, 'VIP'),
  (15, 1, '2026-11-04', 'paid',     35, NULL);
```

![The two sample tables: five customers, one with a NULL plan, and six orders](https://cdn.sanity.io/images/upsr56wu/production/d4c555faf1944b735d851bf50991e38cca4aa82e-1344x548.png?w=1280)

FIG 01 — Dev has no plan; customers 4 and 5 have no orders.

## How AI for Database handles COUNT questions

Most COUNT queries in a company are business questions in disguise: how many signups yesterday, how many paying customers, how many orders were refunded. AI for Database connects to PostgreSQL, MySQL, SQL Server, Supabase, BigQuery and other sources, and you ask the question in plain English, for example "How many distinct customers placed a paid order this month?". It writes the `COUNT(DISTINCT ...)` query, runs it read-only, returns the number or a chart, and shows the SQL so you can check it.

Two things go beyond a one-off query:

*   **Self-refreshing dashboards:** pin "orders per day, last 30 days" to a dashboard and it refreshes on a schedule, so nobody re-runs the query.
*   **Action workflows:** set a condition such as "when daily signups drop below 50" and an action (Slack alert, email or webhook); the system watches the count for you.

It does not replace learning SQL if you write queries for a living. It is for the people who need the number and would otherwise wait for an engineer. Pricing starts free with sample data; Pro is $10 per user per month.

## 1\. Total rows in a table

Scenario: how many customers do we have?

```
SELECT COUNT(*) AS total_customers
FROM customers;
-- total_customers: 5
```

`COUNT(*)` counts rows, not values, so Dev's NULL plan does not matter. It includes duplicates and rows that are entirely NULL ([Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql)). Adapt it by changing the table or adding a `WHERE`.

## 2\. What is the difference between COUNT(\*), COUNT(1) and COUNT(column)?

`COUNT(*)` and `COUNT(1)` both count rows; `COUNT(column)` counts only the rows where that column is not NULL.

```
SELECT COUNT(*)    AS all_rows,
       COUNT(1)    AS count_one,
       COUNT(plan) AS with_plan
FROM customers;
-- all_rows: 5 | count_one: 5 | with_plan: 4
```

`COUNT(1)` counts a constant that is never NULL, so it always equals `COUNT(*)`. There is no speed difference worth caring about in PostgreSQL, MySQL or SQL Server; pick `COUNT(*)` because it says what you mean. `COUNT(plan)` returns 4 because Dev's plan is NULL. That gap is the most common source of "why don't my numbers match" bugs.

VIDEO · [Difference Between count(\*) ,count(0),count(-1),count(col),count('youtube') | SQL Interview question](https://www.youtube.com/watch?v=IE8NueisxHY) (Ankit Bansal)

## 3\. How do I count distinct values?

Scenario: how many different customers have bought anything?

```
SELECT COUNT(DISTINCT customer_id) AS buyers,
       COUNT(customer_id)          AS orders_with_customer
FROM orders;
-- buyers: 3 | orders_with_customer: 6
```

`COUNT(DISTINCT expr)` counts unique non-null values: customers 1, 2 and 3. `COUNT(*)` cannot take `DISTINCT` ([Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql)). Use this for "unique buyers", "active users" or "countries we sell to"; our guide to [tracking daily active users from your database](https://www.aifordatabase.com/blog/track-daily-active-users-from-database/) builds DAU on exactly this pattern.

## 4\. Count values in a nullable column

Scenario: how many orders used a coupon, and how many different coupons?

```
SELECT COUNT(*)                    AS orders,
       COUNT(coupon_code)          AS with_coupon,
       COUNT(DISTINCT coupon_code) AS coupon_types
FROM orders;
-- orders: 6 | with_coupon: 3 | coupon_types: 2
```

Three orders have a coupon (FALL10 twice, VIP once), and there are two distinct codes. Subtract to get the rest: `COUNT(*) - COUNT(coupon_code)` gives 3 orders without a coupon.

## 5\. How do I count rows that match a condition?

Scenario: paid vs refunded orders in one row. PostgreSQL (9.4+) and SQLite support the standard `FILTER` clause on any aggregate ([PostgreSQL docs](https://www.postgresql.org/docs/current/sql-expressions.html)):

```
SELECT COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
       COUNT(*) FILTER (WHERE status = 'refunded') AS refunded
FROM orders;
-- paid: 5 | refunded: 1
```

MySQL and SQL Server do not have `FILTER`; use `CASE` inside `COUNT`. A `CASE` without `ELSE` returns NULL, and `COUNT` skips NULLs:

```
SELECT COUNT(CASE WHEN status = 'paid' THEN 1 END)     AS paid,
       COUNT(CASE WHEN status = 'refunded' THEN 1 END) AS refunded
FROM orders;
-- paid: 5 | refunded: 1
```

A common bug is `COUNT(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)`: 0 is not NULL, so it returns 6. Use `SUM(...)` with `ELSE 0`, or `COUNT(...)` without the `ELSE`. Our [SQL COUNTIF guide](https://www.aifordatabase.com/blog/sql-countif/) covers more conditional patterns.

## 6\. Count per group with GROUP BY

Scenario: orders per day.

```
SELECT created_at, COUNT(*) AS orders
FROM orders
GROUP BY created_at
ORDER BY created_at;
-- 2026-11-01 | 3
-- 2026-11-03 | 2
-- 2026-11-04 | 1
```

Every non-aggregated column in the `SELECT` must be in the `GROUP BY`. Notice that 2026-11-02 is missing: no rows, no group. Example 7 fixes that.

## 7\. How do I show zero counts for days with no rows?

Generate the full list of days, `LEFT JOIN` the orders, and count a column from the orders side. In PostgreSQL, `generate_series` builds the days ([PostgreSQL docs](https://www.postgresql.org/docs/current/functions-srf.html)):

```
SELECT d::date AS day, COUNT(o.id) AS orders
FROM generate_series('2026-11-01'::date, '2026-11-04'::date, interval '1 day') AS d
LEFT JOIN orders o ON o.created_at = d::date
GROUP BY d
ORDER BY d;
-- 2026-11-01 | 3
-- 2026-11-02 | 0
-- 2026-11-03 | 2
-- 2026-11-04 | 1
```

Use `COUNT(o.id)`, not `COUNT(*)`. The empty day still produces one joined row with NULL order columns, so `COUNT(*)` would report 1 order on 2026-11-02. On MySQL 8 build the days with a recursive CTE; on SQL Server use a calendar table or `GENERATE_SERIES` (SQL Server 2022+).

![COUNT(*) counts the empty day's joined row as 1, COUNT(o.id) correctly returns 0](https://cdn.sanity.io/images/upsr56wu/production/0ca54f04bcc8b0271baf8b866f90bd5eeb03535a-1344x594.png?w=1280)

FIG 02 — Count a column from the joined table to get true zeros.

## 8\. Count related rows per customer, including zero

Scenario: orders per customer, so you can spot who never bought.

```
SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;
-- Ana  | 3
-- Cara | 2
-- Ben  | 1
-- Dev  | 0
-- Eli  | 0
```

Same rule as example 7: count the right-hand table's key. An inner `JOIN` would drop Dev and Eli entirely. See our [JOIN glossary entry](https://www.aifordatabase.com/glossary/join/) for how row counts change across join types.

## 9\. Filter groups by their count with HAVING

Scenario: repeat customers, with two or more orders.

```
SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 2
ORDER BY customer_id;
-- 1 | 3
-- 3 | 2
```

`WHERE` filters rows before grouping; `HAVING` filters groups after counting. You cannot write `WHERE COUNT(*) >= 2`.

## 10\. Window totals with COUNT() OVER (PARTITION BY)

Scenario: list each order with how many orders that customer has in total, without collapsing the rows.

```
SELECT id, customer_id,
       COUNT(*) OVER (PARTITION BY customer_id) AS customer_orders
FROM orders
ORDER BY id;
-- 10 | 1 | 3
-- 11 | 1 | 3
-- 12 | 2 | 1
-- 13 | 3 | 2
-- 14 | 3 | 2
-- 15 | 1 | 3
```

The window form keeps every row and attaches the group count. SQL Server, PostgreSQL and MySQL 8 support it ([Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql)).

## Which COUNT example should you use?

| You want | Use | Example |
| --- | --- | --- |
| Total rows | `COUNT(*)` | 1 |
| Rows where a column has a value | `COUNT(column)` | 2, 4 |
| Unique values | `COUNT(DISTINCT column)` | 3 |
| Rows matching a condition | `FILTER (WHERE ...)` or `COUNT(CASE ...)` | 5 |
| Count per group | `GROUP BY` | 6 |
| Zero for empty days or customers | `LEFT JOIN` + `COUNT(right.id)` | 7, 8 |
| Only groups above a threshold | `HAVING COUNT(*) >= n` | 9 |
| Count beside each row | `COUNT(*) OVER (PARTITION BY ...)` | 10 |

## Database differences that change COUNT results

*   **Return type:** PostgreSQL returns `bigint`. SQL Server `COUNT` returns `int` and fails with "Msg 8115, Arithmetic overflow error converting expression to data type int" above 2,147,483,647 rows; use `COUNT_BIG` there ([Microsoft Learn](https://learn.microsoft.com/en-us/sql/t-sql/functions/count-transact-sql)).
*   **Empty input:** `COUNT` returns 0 when no rows match, while `SUM`, `AVG` and the other aggregates return NULL ([PostgreSQL docs](https://www.postgresql.org/docs/current/functions-aggregate.html)).
*   **Conditional counts:** `FILTER` works in PostgreSQL and SQLite; use `CASE` in MySQL and SQL Server.

To run any of these on a schedule rather than by hand, see [how to schedule a Postgres query](https://www.aifordatabase.com/blog/postgres-schedule-query/), or the [signup funnel for Postgres](https://www.aifordatabase.com/answers/signup-funnel-postgres/) answer page for counts chained into a funnel.

## Next step

Copy the example closest to your question and swap in your table names. Or skip the SQL: Try AI for Database free at aifordatabase.com ([sign up here](https://app.aifordatabase.com/signup), sample data included).

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What is the difference between COUNT(), COUNT(1) and COUNT(column)?

COUNT() and COUNT(1) both count every row and always return the same number. COUNT(column) counts only rows where that column is not NULL. On the sample customers table they return 5, 5 and 4.

### How do I count distinct values in SQL?

Use COUNT(DISTINCT column). It counts unique non-null values, so SELECT COUNT(DISTINCT customer\_id) FROM orders returns 3 for six orders from three customers.

### How do I count rows that match a condition?

Put the condition in WHERE for a single count, or use COUNT() FILTER (WHERE condition) in PostgreSQL and COUNT(CASE WHEN condition THEN 1 END) in MySQL and SQL Server to get several conditional counts in one row.

### How do I show zero counts for days with no rows?

Generate every day (generate\_series in PostgreSQL, a recursive CTE in MySQL), LEFT JOIN your table, and count a column from that table such as COUNT(o.id). COUNT() would count the empty joined row as 1.

### Does COUNT return NULL when no rows match?

No. COUNT returns 0 for no matching rows. Other aggregates such as SUM return NULL, so wrap those in COALESCE(SUM(x), 0).

### Is COUNT() slow on large tables?

An exact COUNT() must read every row or a full index, so on tables with hundreds of millions of rows it can take seconds. For dashboards, count over a filtered time range or keep a pre-aggregated daily table.

Read next

*   [SAAS BENCHMARKS REPORT GUIDE: 10 REPORTS COMPARED →](https://www.aifordatabase.com/blog/saas-benchmarks-report/)
*   [8 BEST TOOLS TO VISUALIZE SQL RESULTS AND SCHEMAS →](https://www.aifordatabase.com/blog/visualize-sql-tools/)
*   [SQL COUNTIF: HOW TO COUNT ROWS THAT MATCH A CONDITION →](https://www.aifordatabase.com/blog/sql-countif/)
*   [POSTGRES BOOLEAN TYPE: VALUES, NULL LOGIC AND COUNTING →](https://www.aifordatabase.com/blog/postgres-boolean/)

Source: https://www.aifordatabase.com/blog/sql-count-statement-examples/
