MySQL COUNT(DISTINCT) function

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

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)

Previous: COUNT() with group by
Next: GROUP_CONCAT()