COUNT

• It returns the number of rows in a table
• The COUNT() function allows you to count all rows or only rows that match a specified condition.

The COUNT() function has three forms:

• COUNT(*)
• COUNT(expression)
• COUNT(DISTINCT expression)

---

COUNT(*) function

The COUNT(*) function returns the number of rows in a result set returned by a SELECT FROM statement. The COUNT(*) returns the number of rows including duplicate, non-NULL and NULL rows.

COUNT(expression)

The COUNT(expression) returns the number of rows that do not contain NULL values as the result of the expression.

COUNT(DISTINCT expression)

The COUNT(DISTINCT expression) returns the number of distinct rows that do not contain NULL values as the result of the expression.

The return type of the COUNT() function is BIGINT. The COUNT()  function returns 0 if there is no matching row found.

Example

First, Create table:

CREATE TABLE count_demos (
	id INT AUTO_INCREMENT PRIMARY KEY,
	val INT
);

Second, insert some rows:

INSERT INTO count_demos(val)
VALUES (1), (1),(2),(2),(NULL),(3),(4),(NULL),(5);

Third, query data:

SELECT * FROM count_demos;

COUNT(*)

return all rows

SELECT COUNT(*) FROM count_demos;

We can specify a condition to count only what I want with WHERE

SELECT COUNT(*)
FROM count_demos
WHERE val = 2;

COUNT(expression)

If you specify the val column in the COUNT() function, the COUNT() function will count only rows with non-NULL values in the val column:

SELECT COUNT(val)
FROM count_demose;

Notice that two NULL values are not counted.

COUNT(DISTINCT expression)

This example uses COUNT(DISTINCT expression) to count non-NULL and distinct values in the column val:

SELECT COUNT(DISTINCT val)
FROM count_demos;