SQL COUNT: COUNT(*), COUNT(Column) and COUNT DISTINCT

Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.

Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(40),
  manager_id INTEGER
);
CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER,
  amount DECIMAL(10,2),
  status VARCHAR(20) NOT NULL,
  created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
  (1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
  (3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
  (101,1,10,'paid','2026-01-01'),
  (102,1,30,'pending','2026-01-02'),
  (103,2,20,'paid','2026-01-02'),
  (104,3,NULL,'pending','2026-02-01'),
  (105,3,20,'paid','2026-02-02'),
  (106,NULL,5,'paid','2026-02-03');

An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.

The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.

COUNT(*) counts every row in its input, including a row whose optional fields are all missing. The sales table has six rows, so its row count is six. COUNT(amount) counts the rows where amount is present, giving five. It does not add the amounts, and it does not ignore a known zero. The difference between those two counts reveals one row needing an amount, provided both counts use the same input and filters.

COUNT(DISTINCT amount) counts distinct known numeric amounts. The known values are five, ten, twenty and thirty, so the answer is four even though five sale rows have known amounts. COUNT(DISTINCT customer_id) gives three identified customers with sales. Neither figure includes the anonymous customer link. If you need to count distinct combinations of several fields portably, select those combinations with DISTINCT in a derived table and count its rows.

An aggregate COUNT without GROUP BY returns a summary row containing zero when the filtered input is empty. With GROUP BY, absent groups do not appear unless you first build a complete set of entities and join data to it. To list every customer with a sale count, start from customers and LEFT JOIN sales. Then count the joined sales.id, not COUNT(*), because the outer join creates a placeholder row for a customer with no sale. Dev’s true sale count should be zero, not one.

Counts after joins measure the joined rows. Joining sales to another one-to-many table can multiply each sale and inflate COUNT(*). DISTINCT can sometimes express the intended entity count, but inspect the relationships before using it as a patch. Include a clear grain in the output name, such as sale_count or customer_count. For very large datasets, the result type and potential overflow vary between engines; SQL Server offers COUNT_BIG when its regular integer COUNT is insufficient.

The three single-expression forms are supported by all four engines. Syntax for counting distinct multiple columns differs, so use a distinct subquery for portability. SQL Server COUNT returns int and COUNT_BIG returns bigint. Do not assume a row count can be answered from table metadata without evaluating relevant visibility and filter rules.

Check the engine-specific syntax and version requirements in MySQL COUNT aggregate.

Return every customer and the number of matching sale records, including Dev with zero.

Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.

SELECT c.id,c.name,COUNT(s.id) AS sale_count
FROM customers AS c
LEFT JOIN sales AS s ON s.customer_id=c.id
GROUP BY c.id,c.name ORDER BY c.id;

id | name | sale_count
1 | Ana | 2
2 | Ben | 1
3 | Cara | 2
4 | Dev | 0

The left join keeps each customer. Counting the sale primary key counts only actual matches, including Cara’s sale with an unknown amount. Grouping by customer id and name supplies one summary per customer, and the unlinked sale belongs to no customer group.

Compare COUNT(), COUNT(amount) and COUNT(DISTINCT amount) after filtering to paid status: expect four, four and three. Then left join every customer and compare COUNT() with COUNT(s.id) for Dev, observing one versus zero. These controlled examples distinguish record population, known-value presence, repeated measures and the outer-join placeholder without changing source data.