SELECT DISTINCT vs GROUP BY in MySQL?

MySQLMySQLi Database

SELECT DISTINCT can be used to give distinct values. Use it to remove duplicate records and it can be used with aggregate function as well. For example: MAX, AVG etc. This can be applied on a single column.

Now, I am creating a table to use SELECT DISTINCT for a column. Creating a table with the help of CREATE command −

mysql> CREATE TABLE DistinctDemo
-> (
-> id int,
-> name varchar(100)
-> );
Query OK, 0 rows affected (0.64 sec)

Inserting records −

mysql> INSERT into DistinctDemo values(1,'John');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT into DistinctDemo values(2,'John');
Query OK, 1 row affected (0.18 sec)

mysql> INSERT into DistinctDemo values(3,'Bob');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT into DistinctDemo values(4,'John');
Query OK, 1 row affected (0.15 sec)

mysql> INSERT into DistinctDemo values(5,'David');
Query OK, 1 row affected (0.17 sec)

mysql> INSERT into DistinctDemo values(6,'Bob');
Query OK, 1 row affected (0.16 sec)

Displaying all the records with the help of SELECT command. The query is as follows −

mysql> SELECT * from DistinctDemo;

After executing the above query, we will get the following output −

+------+-------+
| id   | name  |
+------+-------+
| 1    | John  |
| 2    | John  |
| 3    | Bob   |
| 4    | John  |
| 5    | David |
| 6    | Bob   |
+------+-------+
6 rows in set (0.00 sec)

Above, we have six records where John and Bob is duplicate. We can apply DISTINCT to remove the duplicate records. The syntax is as follows −

SELECT distinctcolumn_name from yourTableName order by column_name;

Now, I am applying the above query to remove duplicates −

mysql> SELECT distinct name from DistinctDemo;

The following is the output

+-------+
| name  |
+-------+
| John  |
| Bob   |
| David |
+-------+
3 rows in set (0.00 sec)

Select group by can be used to get data from different columns and group into one or more column. This can also be applied with aggregate function.For Example:SUM,AVG etc.

Firstly, I am creating a table with the help of CREATE command −

mysql> CREATE table GroupDemo1
-> (
-> id int,
-> name varchar(100),
-> address varchar(100)
-> );
Query OK, 0 rows affected (0.68 sec)

After creating the table, i am inserting the record into the table which is as follows −

mysql> INSERT into GroupDemo1 values(1,'John','US');
Query OK, 1 row affected (0.18 sec)

mysql> INSERT into GroupDemo1 values(2,'Bob','UK');
Query OK, 1 row affected (0.13 sec)

mysql> INSERT into GroupDemo1 values(3,'David','US');
Query OK, 1 row affected (0.12 sec)

mysql> INSERT into GroupDemo1 values(4,'David','US');
Query OK, 1 row affected (0.15 sec)

Now, we can display all the records with the help of SELECT command −

mysql> SELECT * from GroupDemo1;

The following is the output −

+------+-------+---------+
| id   | name  | address |
+------+-------+---------+
| 1    | John  | US      |
| 2    | Bob   | UK      |
| 3    | David | US      |
| 4    | David | US      |
+------+-------+---------+
4 rows in set (0.00 sec)

I will apply the GROUP BY to know the same address with number of occurrences. Here is the syntax −

SELECT column_name1,......N aggregate function( ) from yourTableName group by
column_name;

Let us apply the above syntax to GROUP BY −

mysql> SELECT address, count(*) from GroupDemo1 group by address;

The following is the output −

+---------+----------+
| address | count(*) |
+---------+----------+
| US      | 3        |
| UK      | 1        |
+---------+----------+
2 rows in set (0.00 sec)
raja
Published on 18-Oct-2018 16:21:37
Advertisements