MySQL COUNT function

The count function returns the number of rows in the table. This function shows either the entire rows count or the required row count of the table. It determines the count of the data set. It works on numerical data type values. If the table displays zero value, the row does not exist in the table.

The count function can use several arguments inside of the bracket. The count function represents in three forms whose description shows below:

• COUNT(*): It returns the count of all duplicate, null, and not null rows of the table.
• COUNT(function expression): It displays all duplicate and not null rows of the table. You cannot show null rows of the table.
• COUNT(DISTINCT expression): This argument shows unique and not null rows of the table. You cannot show duplicate and null rows of the table.

Syntax

The COUNT () function syntax shows below.

COUNT(function_expression)

The COUNT () function with table syntax displays below.

SELECT COUNT(function_expression) FROM Table_Name;

COUNT () function with table data syntax displays below.

SELECT COUNT(*) FROM Table_Name;

COUNT () function with distinct clause syntax displays below.

SELECT COUNT(DISTINCT function_expression) FROM Table_Name;

COUNT () function with condition syntax shows below.

SELECT COUNT(function_expression) FROM Table_Name WHERE condition;

Description

• The COUNT() function returns a number of the rows of the table.
• The "function_expression" represents the rows for function operation.
• The "WHERE" clause applies to filter the records of the table.
• The "*" symbol shows the entire table data and values for the COUNT function.
• The DISTINCT clause removes duplicate data of the given columns.

Prerequisite for COUNT function

• Create a new table using the below statements.

mysql> create table arts (  

    product_id int NOT NULL,

    product_name varchar(100) NOT NULL,    

    available_quantity int,  

    required_quantity int,  

    Date date

    );  

• Insert the data in the table.

mysql> insert into arts(product_id, product_name, available_quantity,  

required_quantity, Date) VALUES      

(1, 'paper', 78, 45, '2021-08-11'),  

(2, 'sketch book', 100, 65, '2021-09-01'),  

(3, 'pencil', 78, 45, '2021-10-14'),  

(4, 'brush pen', 85, 25, '2021-12-21'),  

(5, 'eraser', 95, 56, '2021-07-23'),  

(6, 'sketch pen', 99, 66, '2021-10-13');

• You can refer to the table structure and information using the output query.

mysql> SELECT * FROM arts;

The table shows the five columns and six rows.

Examples of the COUNT function

1) Example: This example shows the COUNT function with expression and its output.

mysql> SELECT COUNT(required_quantity) AS products FROM arts;

 Output

The count function displays six rows of the product column.  

2) Example: This example displays the COUNT function with the * argument and its output. The "*" symbol counts entire columns and rows of the table.

mysql> SELECT COUNT(*) AS products FROM arts;

 Output

The output image shows all available rows of the table. The query can contain null or not null values. The "arts" table contains six columns of the entire columns.

3) Example: This example uses the DISTINCT clause in the COUNT function. This DISTINCT clause applies to the "required_quantity" column of the "arts" table. The DISTINCT function removes duplicate values of the given column.

mysql> SELECT COUNT(DISTINCT required_quantity) AS products FROM arts;

 Output

The output image shows the table has five unique rows. The distinct clauses remove the "45" data of the required_quantity column.

4) Example: The COUNT functions with multiple columns example.

Execute the below query with the product_id, available_quantity,  required_quantity, and Date columns in the count function.

mysql> SELECT COUNT(product_id) AS id,
COUNT(available_quantity) AS availability,
COUNT(required_quantity) AS necessity,
 COUNT(Date) AS received 
FROM arts;

Output

5) Example: The COUNT functions using the "WHERE" clause example.

Execute the COUNT function with multiple columns of the table. This function uses the WHERE clause for the conditional operation.

mysql> SELECT COUNT(product_id) AS id,
COUNT(available_quantity) AS availability,
COUNT(required_quantity) AS necessity,
 COUNT(Date) AS received 
FROM arts WHERE available_quantity < 90;

Output

MySQL count function exhibits three rows of the "arts" table. These rows represent less than 90 values of the available_quantity column.

6) Example: The COUNT functions with "WHERE" and "DISTINCT" clauses example shows below. The "DISTINCT" clause is used on all columns and the "WHERE" clause on a single column.

mysql> SELECT COUNT(DISTINCT product_id) AS id,
COUNT(DISTINCT available_quantity) AS availability,
COUNT(DISTINCT required_quantity) AS necessity,
 COUNT(DISTINCT Date) AS received 
FROM arts WHERE available_quantity < 90;

Output

The count function shows the output table having four columns and one clause. The "WHERE" clause uses the "less than 90 value" of the "available_quantity" column. The first and last columns show three counts of the data set. The second and third rows show two counts of the data set because of the DISTINCT clause.

7) Example: The COUNT functions with the "GROUP BY" clause example shows below. The "GROUP BY" uses the "available_quantity" column of the "arts" table. You can see the numerical and date data type column in the count function query.

mysql> SELECT COUNT(product_id) AS id,
COUNT(available_quantity) AS availability,
COUNT(required_quantity) AS necessity,
 COUNT(Date) AS received 
FROM arts GROUP BY available_quantity;

Output

The output image represents the table values using the count function. The first row shows similar values of the available_quantity column.  The other rows show one value that represents the unique row of the column.

8) Example: The COUNT functions with the "GROUP BY" and "HAVING" clause example displays below. The "GROUP BY" uses the "available_quantity" column, and the HAVING clause applies the "greater than" condition.

mysql> SELECT COUNT(product_id) AS id,
COUNT(available_quantity) AS availability,
COUNT(required_quantity) AS necessity,
 COUNT(Date) AS received 
FROM arts GROUP BY available_quantity HAVING available_quantity > 80;

Output

MySQL count function shows four rows of the table as output. This table displays Boolean values using the "HAVING" condition.

9) Example: The COUNT functions using the "limit" clauses example shows below.

MySQL count function comes with the "limit" clause. You can use the limit clause with the other MySQL clauses and conditions.

mysql> SELECT COUNT(product_id) AS id,
COUNT(available_quantity) AS availability,
COUNT(required_quantity) AS necessity,
 COUNT(Date) AS received 
FROM arts GROUP BY available_quantity HAVING available_quantity > 80 limit 2;

Output

The count function executes two rows because of the limit clause and its two (2) values. The output returns Boolean values as per the condition.