MySQL COUNT And COUNT DISTINCT With Examples - Chia Sẻ Kiến Thức Điện Máy Việt Nam

Learn about the usages of various forms of the MySQL COUNT function with examples:

COUNT is a simple aggregate function, yet very effective and widely used. In very simple words, it’s used to COUNT the number of rows against a given SELECT query and criteria.

This tutorial will explain the syntax and usage of simple COUNT, COUNT with conditions, COUNT with DISTINCT, COUNT with GROUP BY, etc.

=> Click here for the complete MySQL tutorial series

Various Types Of MySQL COUNT

TypeDescriptionSyntax

COUNT(*)COUNT(*) function returns the no. of rows retrieved by the SELECT statement including rows containing NULL and Duplicate valuesSELECT COUNT(*) FROM {tableName}

COUNT(Expression)COUNT(expression) would count the value where expression is not null.
Expression can be something simple like a column name or a complex expression such as IF Function.SELECT COUNT(Expression) from {tableName}

COUNT(DISTINCT Expression)COUNT(DISTINCT expression) – DISTINCT keyword would result in counting only unique non null values against the expression.
For example – COUNT(DISTINCT customerName) – would only count rows having distinct values for customer nameSELECT COUNT(DISTINCT expression) from {tableName}

MySQL COUNT Examples

Test Data

We would be using the following tables and data for the examples for the MySQL COUNT function.

Tables:

#1) Product_Details

Stores details of various products in a store

product_id – INT
product_name – VARCHAR
price – DECIMAL
category_id – INT (FOREIGN KEY – id from Category_Details table)

#2) Category_Details:

category_id : INT
category_name : VARCHAR

Let’s create the tables and insert dummy data using the queries below:

CREATE TABLE `product_details` (
  `product_id` int NOT NULL,
  `product_name` varchar(100),
  `price` decimal(5,2),
  `category_id` int,
  FOREIGN KEY (category_id) REFERENCES category_details(category_id),
  PRIMARY KEY(product_id)
)

CREATE TABLE `category_details` (
  `category_id` int NOT NULL,
  `category_name` varchar(100),
  PRIMARY KEY(category_id)
)

INSERT INTO `category_details` (`category_id`,`category_name`) VALUES 
(1,'FMCG'),(2,'FURNITURE'),(3,'FASHION'),(4,'APPLIANCES'),(5,'ELECTRONICS');

INSERT INTO `product_details`
 (`product_id`,`product_name`,`price`,`category_id`) VALUES
 (1,'Biscuits',0.5,1),(2,'Chocolates',1,1), (3, 'Washing Powder',5,1),(4,
 'Apple IPhone', 500,5), 
(5, 'Batteries',2,5),(6,'Floor cleaner',2.5,1),(7,'Jeans-
Levis',100,1),(8,'Mixer grinder',50,4),(9,'Capsicum',2,1),(10,'Sugar',1,1),(11,'Study Table',50,2);

Please refer to images of the tables with the test data as created above.

Product_Details Table

product_details

Category_Details Table

category_details

Simple COUNT

In this section, we will see the COUNT function in the most simplistic way.

We will use COUNT to return the number of rows without any NULL or DISTINCT checks.

SELECT COUNT(*) FROM product_details;

Output:

COUNT(*)

With the above query, we will just get the number of rows returned against the select statement.

COUNT With Conditions

Now we will use the COUNT function with conditions or column values.

Example: Suppose we want to count the no. of rows where there are no non-null values for product-names.

We can add product_name (or any given column name) as the expression in the COUNT function, which would then result in a count of rows having NON NULL product_name rows.

SELECT COUNT(product_name) FROM product_details;

In this case, the output will be 11 (as all the test data already has values for product_name column)

Output:

COUNT(product_name)

Let’s add a new row, with a NULL value for product_name.

INSERT INTO `product_details` (`product_id`,`product_name`,`price`,`category_id`) VALUES (12,NULL,0.5,1);

SELECT COUNT(product_name) from product_details;

The above COUNT query will return count of all rows having a NON NULL product_name.

Since we inserted a row with NULL product_name, that would not be returned in the results. So the output would be still 11 rows.

Output:

COUNT(product_name)

COUNT With DISTINCT

In the previous examples, we used the COUNT function with an expression. We can also combine the expression with a DISTINCT command to get all the NON NULL values, which are UNIQUE as well.

Let’s try to get the DISTINCT category_id from the product_details table.

SELECT COUNT(DISTINCT category_id) from product_details;

Output:

COUNT(DISTINCT category_id)

As you can see above – the output is 4, which represents the total no. of category_id’s in table product_details.

COUNT With GROUP BY

Let’s look at an example where we want to use COUNT along with GROUP BY. This is an important combination where we can get a COUNT against a grouped column and analyze data depending on different values of a target grouped column.

For example: Find the no. of products against each category from the product_details table.

SELECT category_id, COUNT(*) FROM product_details GROUP BY category_id;

category_idCOUNT(*)

As we can see above, against each category_id the COUNT(*) column represents the number of rows belonging to each category_id.

COUNT With IF

Let’s see an example of using an IF condition inside the COUNT function. We can place an IF expression inside the COUNT function and set the value to NULL for false condition and any non-null value for the true condition.

Every NON NULL value would be counted as a single row w.r.t COUNT function.

For example: Use the COUNT function to find all the products within the price range of 20$.

SELECT COUNT(IF(price &gt;0 AND price&lt;20, 1, NULL)) AS count_less_than_20 FROM product_details;

Output:

count_less_than_20

In the above query, we have obtained the COUNT of all products whose price range is between 0 & 20. For the FALSE condition, we have set the value to NULL, which does not get counted, when the row is evaluated for different column values.

COUNT With JOINS

COUNT can be used with JOIN statements as well. Since COUNT applies to no. of rows, it can be used with any combination of a query operating on a single table or multiple table using JOINS.

Example: Join both product_details and category_details table and find count by category_name from the product_details table.

SELECT category_name, COUNT(category_name) from product_details pd INNER 
JOIN category_details cd ON cd.category_id = pd.category_id
GROUP BY category_name;

Output:

category_nameCOUNT(category_name)

FMCG7

FURNITURE1

APPLIANCES1

ELECTRONICS2

Tip & Trick

Using Column alias for the column that displays the result of the COUNT function: We can use a Column alias along with COUNT function to have user defined column names for the column displaying the results of the COUNT function.

For example: Suppose we want to count the number of categories in category_details table and name the resulting column as category_count, we can use below query:

SELECT COUNT(*) as category_count from category_details;

Output:

category_count

Frequently Asked Questions

Q #1) How do I use the COUNT function in MySQL?

Answer: COUNT function is an aggregate function that can be used in 3 ways.

COUNT(*) – This would COUNT all the rows returned by the SELECT QUERY.
COUNT(expression) – This would COUNT all the NON NULL values for the expression.
COUNT (DISTINCT expression) – This would COUNT all the NON NULL and UNIQUE values against the expression.

Q #2) What is the difference between Count (*) and Count(1) in SQL?

Answer: Both the statements behave in an identical way. From MySQL’s definition of COUNT, anything in parentheses () is an expression – and any NON NULL value would be counted as 1.

So in this case, both * and 1 is treated as NON NULL and the same result is returned i.e. the result of both the queries below would be the same.

SELECT COUNT(*) from product_details;
SELECT COUNT(1) from product_details;

Conclusion

In this tutorial, we learned about the COUNT function and the different variations provided by MySQL.

We also saw how we can use COUNT with different scenarios, like combining COUNT with GROUP BY and writing an IF function within the COUNT function.

The COUNT function is one of the most important and broadly used functions in MySQL and is largely used to aggregate data depending on the specified conditions for one or more tables.