MySQL COUNT()

In SQL, the COUNT() aggregation function is used to count the number of records in a table. Knowing the number of rows in a table is very useful in many cases, for example, to know how many users are in a table or to know the number of comments on an article.
 

Syntax:

To find out the total number of rows in a table, simply run the following SQL query:

SELECT COUNT(*) FROM table

It is also possible to know the number of records in a particular column. The records that have a null value will not be counted. The syntax to count the records on the column “column_name” is as follows:

SELECT COUNT(column_name) FROM table

 

 
Finally, it is also possible to count the number of unique records for a column. The function will not count duplicates for a selected column. The syntax for counting the number of unique values for the column “column_name” is as follows:

SELECT COUNT(DISTINCT column_name) FROM table

Note: in general, in terms of performance, it is recommended to filter the rows with GROUP BY if it is possible, then run COUNT(*).
 

Example :

Let’s assume a table that lists the users of an e-commerce website:

+-------------+--------+-----------+---------------------+------------------+
|   user_id   |  name  |   city    | number_of_purchases | id_last_purchase |
+-------------+--------+-----------+---------------------+------------------+
|         101 |  Alex  |  New York | 6                   | 4                |
|         102 |  John  |  Chicago  | 0                   | NULL             |
|         103 |  David |  Chicago  | 1                   | 27               |
|         104 |  Emily |  Chicago  | 12                  | 30               |
|         105 |  Jean  |  Mumbai   | 0                   | NULL             |
|         106 |  Paul  |  Sydney   | 0                   | NULL             |
|         107 |  Bob   |  London   | 0                   | NULL             |
+-------------+--------+-----------+---------------------+------------------+

 

Using COUNT(*)

To count the total number of users, simply use COUNT(*) on the entire table:

SELECT COUNT(*) FROM users

Output:

7

 

 

Using COUNT(*) with WHERE

To count the number of users who have made at least one purchase, simply do the same thing but filter the records with WHERE:

SELECT COUNT(*) FROM users WHERE number_of_purchases > 0

Output:

3

 

Using COUNT(column)

Another method is to count the number of users who have made at least one purchase. It is possible to count the number of records in the column “id_last_purchase”. Knowing that the value is null if there is no purchase, the rows will not be counted if there was no purchase. The query is therefore as follows:

SELECT COUNT(id_last_purchase) FROM users

Output:

3

 

 

Using COUNT(DISTINCT column)

The use of the DISTINCT clause can be used to find out the number of distinct cities on which the users are distributed. The query would be as follows

SELECT COUNT(DISTINCT city) FROM users

Since there are 5 different cities (cf. New York, Chicago, Mumbai, Sydney, and London), the result is as follows:
 
Output:

5

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More