Syntax error Using group by on two fields and count in MySQL?

Using group by on two fields and count in MySQL?

To implement GROUP BY on two fields and count, let us create a table. The following is the query to create a table −

mysql> create table GroupByTwoFieldsDemo
   −> (
   −> Id int,
   −> Name varchar(200)
   −> );
Query OK, 0 rows affected (0.53 sec)

Let us insert some records in the table −

mysql> insert into GroupByTwoFieldsDemo values(1,'John');
Query OK, 1 row affected (0.15 sec)

mysql> insert into GroupByTwoFieldsDemo values(10,'Johnson');
Query OK, 1 row affected (0.16 sec)

mysql> insert into GroupByTwoFieldsDemo values(9,'Carol');
Query OK, 1 row affected (0.14 sec)

mysql> insert into GroupByTwoFieldsDemo values(6,'Sam');
Query OK, 1 row affected (0.16 sec)

mysql> insert into GroupByTwoFieldsDemo values(5,'David');
Query OK, 1 row affected (0.20 sec)

mysql> insert into GroupByTwoFieldsDemo values(10,'Johnson');
Query OK, 1 row affected (0.15 sec)

mysql> insert into GroupByTwoFieldsDemo values(6,'Sam');
Query OK, 1 row affected (0.17 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from GroupByTwoFieldsDemo;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
|    1 | John    | 
|   10 | Johnson |
|    9 | Carol   |
|    6 | Sam     |
|    5 | David   |
|   10 | Johnson |
|    6 | Sam     |
+------+---------+
7 rows in set (0.00 sec)

The following is the syntax to group by two columns and count −

select yourColumnName1,yourColumnName2,....N,count(yourColumnName1) from GroupByTwoFieldsDemo group by yourColumnName1 desc,yourColumName2;

Apply the above syntax to group by two columns and display the count of duplicate values −

mysql> select Id,Name,count(Id) from GroupByTwoFieldsDemo group by id desc,Name;

The following is the output −

+------+---------+-----------+
| Id   | Name    | count(Id) |
+------+---------+-----------+
|   10 | Johnson |         2 |
|    9 | Carol   |         1 |
|    6 | Sam     |         2 |
|    5 | David   |         1 |
|    1 | John    |         1 |
+------+---------+-----------+
5 rows in set, 1 warning (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

623 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements