Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Using DISTINCT and COUNT together in a MySQL Query?
We can use DISTINCT and COUNT together in a single MySQL query. Firstly, let us create a table. The CREATE command is used to create a table.
mysql> create table DistCountDemo - > ( - > id int, - > name varchar(100), - > age int - > ); Query OK, 0 rows affected (0.48 sec)
Records are inserted with the help of INSERT command.
mysql> insert into DistCountDemo values(1,'John',23); Query OK, 1 row affected (0.11 sec) mysql> insert into DistCountDemo values(2,'Bob',24); Query OK, 1 row affected (0.14 sec) mysql> insert into DistCountDemo values(3,'John',23); Query OK, 1 row affected (0.16 sec) mysql> insert into DistCountDemo values(4,'Carol',23); Query OK, 1 row affected (0.20 sec)
To display all records with the help of the SELECT statement.
mysql> select *from DistCountDemo;
The following is the output.
+------+-------+------+ | id | name | age | +------+-------+------+ | 1 | John | 23 | | 2 | Bob | 24 | | 3 | John | 23 | | 4 | Carol | 23 | +------+-------+------+ 4 rows in set (0.00 sec)
Applying COUNT as well as DISTINCT to find out the number of students with age 23.
mysql> SELECT COUNT(DISTINCT name) from DistCountDemo WHERE age=23;
The following is the output.
+----------------------+ | COUNT(DISTINCT name) | +----------------------+ | 2 | +----------------------+ 1 row in set (0.05 sec)
Updated on: 30-Jul-2019
356 Views
Advertisements