MySQL count

Syntax of MySQL COUNT() Function

We use the MySQL COUNT() function to get the count of provided expression. The syntax of MySQL COUNT() Function is:

We can also use the MySQL COUNT() function with the GROUP BY clause. But first, look at its syntax when used with the GROUP BY clause.

Parameters of MySQL COUNT() Function

Let us now discuss the various parameters of MySQL's COUNT() function.

1. table: It is the table's name from where we want to fetch the data. We can have one or more tables associated with the FROM clause.
2. WHERE clause: It is an optional parameter used to specify some condition, and based on the specified condition, records are selected from the table.
3. expression: It specifies the condition or the name of the column to be counted.
4. expression-1, expression-2, ... expression-n: It specifies the conditions to be used with the GROUP BY clause.

Return Value of MySQL COUNT() Function

The MySQL COUNT() function returns the count of rows per the specified condition of the query.

We can have three types of expressions associated with the COUNT() function. Let us see them one by one.

1. COUNT(*): The function returns the count of all the rows of the table as per the SELECT query. Here, the * symbol denotes all. It returns NULL, Non-NULL, and duplicate rows of the table.
2. COUNT(expression): It will evaluate the expression on each row and count if the expression is fulfilled. It will finally return the number of rows that satisfy the provided expression without the NULL values.
3. COUNT([DISTINCT] expression): It returns the count of the number of rows containing non-NULL distinct values per the SELECT query statement.

Exceptions of MySQL COUNT() Function

The MySQL COUNT() function does not raise any error or exception if we use it correctly. However, the table must be searched in the database, and the column name or the expression must be valid.

How does the MySQL COUNT() Function Work?

The COUNT() function is one of the aggregate functions used in MySQL. It is used to get the count of records by working on the specified table.

Suppose we have a table of employees (named employee table) containing name, age, salary, id, location, and email address. Now, one of the practical life uses of the MySQL COUNT() function can be getting the number of employees whose location is Delhi.

Specify the table's name (i.e., Employee table), the column name from which we must search or count the records (i.e., location), etc.

The query can be:

Use of COUNT() with GROUP BY

We can use the MySQL COUNT() Function with the GROUP BY clause to get the count of the records that are grouped based on the particular attribute of the table.

Please refer to the next section for an example.

Use of COUNT() without GROUP BY

We can also use the MySQL COUNT() Function without the GROUP BY clause. It will return the count of the records of the specified column or expression.

Please refer to the next section for an example.

Examples

Let us take a few examples better to understand the MySQL COUNT() function.

Example 1: Using MySQL COUNT() function without GROUP BY clause.

Before using the MySQL COUNT() function, let us see how the products table looks.

Let us now use the MySQL COUNT() function to get the number of products added to the products table.

Output:

Here, there are three products (with id-1, 2, 3) added to the table the count came out to be 3.

We can also use the COUNT(*) to get the table records to count. For example:

Output:

Now again when we tried to count the entire rows, the answer came out to be 3 as there were only 3 records in the table.

Example 2: Using MySQL COUNT() function WITH DISTINCT and GROUP BY clause.

Now, to understand the COUNT([DISTINCT]) and COUNT() with GROUP BY clause. But first, let us take the example of another table (Customers).

Let us count the number of different cities, that is, how many cities are in the table.

Output:

In the above example, when we are counting the number of distinct cities i.e. number of unique cities in the table, the counting came out to be 3.

Let us group the people according to the city and use the COUNT() Function.

Output:

In the above example, first, we have grouped the records based on City. After grouping we are counting the number of cities under each group. So, under the city Sagar, the counting came as 2. Similarly for Delhi, Kolkata, etc.

See Also

Please read about other aggregate functions and clauses like:

• SUM()
• AVERAGE()
• MIN()
• MAX()
• GROUP BY
• WHERE
• HAVING

MCQ

Let us test your understanding. Please choose the correct option.

1. What is the return value of the MySQL COUNT() Function?

   Option 1- Number of rows

   Option 2- Smallest value

   Option 3- Average Value

   Option 4- Largest value

   Answer: Option 1 (Number of rows)

2. Can we use MySQL COUNT() Function with a GROUP BY clause?

   Option 1- No

   Option 2- Yes

   Option 3- Maybe

   Answer: Option 2 (Yes)

3. What does COUNT(*) means?

   Option 1- Count Specified rows

   Option 2- Count None rows

   Option 3- Count all rows

   Option 4- None

   Answer: Option 3 (Count all rows)