MySQL COUNT(DISTINCT) function

Last update on March 02 2026 12:06:56 (UTC/GMT +8 hours)

COUNT(DISTINCT) function

MySQL COUNT(DISTINCT) function returns a count of number rows with different non-NULL expr values. It is used to count the number of unique or distinct values that match a specified condition within a table.

This function is useful in -

This helps you understand the diversity and uniqueness of data.
The COUNT(DISTINCT) identifies and eliminates duplicate values in your data. This is useful for data cleaning and ensuring data accuracy.
COUNT(DISTINCT) helps in getting this count or categorical data, you might want to know the number of unique categories or options available.
COUNT(DISTINCT) helps in generating the number of unique occurrences of certain attributes to get accurate and informative reports.
You can analyze membership in various groups or categories by counting the number of distinct members in each group.
COUNT(DISTINCT) provides unique value counts for analysis and decision-making

Syntax:

COUNT(DISTINCT expr,[expr...])

Where expr is a given expression.

MySQL Version: 8.0

Example: MySQL COUNT(DISTINCT) function

The following MySQL statement will count the unique 'pub_lang' and average of 'no_page' up to 2 decimal places for each group of 'cate_id'.

Sample table: book_mast

+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| book_id | book_name                           | isbn_no     | cate_id | aut_id | pub_id | dt_of_pub  | pub_lang | no_page | book_price |
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+
| BK001   | Introduction to Electrodynamics     | 0000979001  | CA001   | AUT001 | P003   | 2001-05-08 | English  |     201 |      85.00 |
| BK002   | Understanding of Steel Construction | 0000979002  | CA002   | AUT002 | P001   | 2003-07-15 | English  |     300 |     105.50 |
| BK003   | Guide to Networking                 | 0000979003  | CA003   | AUT003 | P002   | 2002-09-10 | Hindi    |     510 |     200.00 |
| BK004   | Transfer  of Heat and Mass          | 0000979004  | CA002   | AUT004 | P004   | 2004-02-16 | English  |     600 |     250.00 |
| BK005   | Conceptual Physics                  | 0000979005  | CA001   | AUT005 | P006   | 2003-07-16 | NULL     |     345 |     145.00 |
| BK006   | Fundamentals of Heat                | 0000979006  | CA001   | AUT006 | P005   | 2003-08-10 | German   |     247 |     112.00 |
| BK007   | Advanced 3d Graphics                | 0000979007  | CA003   | AUT007 | P002   | 2004-02-16 | Hindi    |     165 |      56.00 |
| BK008   | Human Anatomy                       | 0000979008  | CA005   | AUT008 | P006   | 2001-05-17 | German   |      88 |      50.50 |
| BK009   | Mental Health Nursing               | 0000979009  | CA005   | AUT009 | P007   | 2004-02-10 | English  |     350 |     145.00 |
| BK010   | Fundamentals of Thermodynamics      | 0000979010  | CA002   | AUT010 | P007   | 2002-10-14 | English  |     400 |     225.00 |
... ... ...
+---------+-------------------------------------+-------------+---------+--------+--------+------------+----------+---------+------------+

View the table

Code:


-- This SQL query calculates the count of distinct publishing languages and the rounded average number of pages for books in each category.
SELECT cate_id, -- Selects the category ID
       COUNT(DISTINCT(pub_lang)), -- Calculates the count of distinct publishing languages for books in each category
       ROUND(AVG(no_page), 2) -- Rounds the average number of pages for books in each category to 2 decimal places
FROM book_mast -- Specifies the table from which to retrieve data (book_mast table)
GROUP BY cate_id; -- Groups the results by category ID, so that the count and average are calculated for each category separately

Explanation:

This SQL query retrieves data from the book_mast table.

It calculates the count of distinct publishing languages and the rounded average number of pages for books in each category.

The GROUP BY clause ensures that the results are grouped by category ID, allowing for separate counts and averages to be calculated for each category.

Here's how the process works:

The query selects the category ID (cate_id).
It calculates the count of distinct publishing languages for books in each category using COUNT(DISTINCT(pub_lang)).

It also calculates the average number of pages for books in each category using AVG(no_page) and rounds the result to 2 decimal places using ROUND().

The results are then grouped by category ID using GROUP BY cate_id, ensuring that the count and average are calculated separately for each category.

Finally, the query returns the category ID, the count of distinct publishing languages, and the rounded average number of pages for books in each category.

Output:

mysql> SELECT cate_id,COUNT(DISTINCT(pub_lang)), ROUND(AVG(no_page),2)
    -> FROM book_mast
    -> GROUP BY cate_id;
+---------+---------------------------+-----------------------+
| cate_id | COUNT(DISTINCT(pub_lang)) | ROUND(AVG(no_page),2) |
+---------+---------------------------+-----------------------+
| CA001   |                         2 |                264.33 | 
| CA002   |                         1 |                433.33 | 
| CA003   |                         2 |                256.67 | 
| CA004   |                         3 |                246.67 | 
| CA005   |                         3 |                245.75 | 
+---------+---------------------------+-----------------------+
5 rows in set (0.00 sec)

PREV : COUNT() with group by
NEXT : GROUP_CONCAT()