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:
SELECT cate_id,COUNT(DISTINCT(pub_lang)), ROUND(AVG(no_page),2)
FROM book_mast
GROUP BY cate_id;
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()
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook