क्या MySQL में COUNT() और DISTINCT एक साथ उपयोग कर सकते हैं?

हां, केवल विशिष्ट (distinct) पंक्तियों की गिनती प्रदर्शित करने के लिए COUNT() और DISTINCT एक साथ उपयोग कर सकते हैं।

सिंटैक्स

SELECT COUNT(DISTINCT yourColumnName) AS anyVariableName 
FROM yourTableName;

उदाहरण के साथ समझें

सबसे पहले एक टेबल बनाते हैं:

mysql> CREATE TABLE CountDistinctDemo (
    -> Id INT NOT NULL AUTO_INCREMENT,
    -> Name VARCHAR(20),
    -> PRIMARY KEY(Id)
    -> ); 
Query OK, 0 rows affected (3.11 sec)

रिकॉर्ड डालें

mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.48 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Bob'); 
Query OK, 1 row affected (0.43 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.26 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('John'); 
Query OK, 1 row affected (0.27 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Bob'); 
Query OK, 1 row affected (0.35 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.98 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('John'); 
Query OK, 1 row affected (0.26 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Sam'); 
Query OK, 1 row affected (0.14 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Mike'); 
Query OK, 1 row affected (0.53 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('Carol'); 
Query OK, 1 row affected (0.31 sec)
mysql> INSERT INTO CountDistinctDemo(Name) VALUES('David'); 
Query OK, 1 row affected (0.40 sec)

सभी रिकॉर्ड देखें

mysql> SELECT * FROM CountDistinctDemo;

आउटपुट

+----+-------+
| Id | Name  |
+----+-------+
|  1 | Carol |
|  2 | Bob   |
|  3 | Carol |
|  4 | John  |
|  5 | Bob   |
|  6 | Carol |
|  7 | John  |
|  8 | Sam   |
|  9 | Mike  |
| 10 | Carol |
| 11 | David |
+----+-------+
11 rows in set (0.07 sec)

बिना DISTINCT के COUNT()

mysql> SELECT COUNT(Name) AS TotalName FROM CountDistinctDemo;

आउटपुट

+-----------+
| TotalName |
+-----------+
| 11        |
+-----------+
1 row in set (0.04 sec)

COUNT() और DISTINCT एक साथ

mysql> SELECT COUNT(DISTINCT Name) AS UniqueName FROM CountDistinctDemo;

आउटपुट

+------------+
| UniqueName |
+------------+
| 6          |
+------------+
1 row in set (0.00 sec)

कुल 11 पंक्तियां हैं लेकिन केवल 6 अद्वितीय नाम हैं (Carol, Bob, John, Sam, Mike, David)।