SQL GROUP BY

<<Previous - SQL ORDER BY

Next - SQL HAVING CLAUSE >>

• GROUP BY clause is used to identify similar data and make them into groups.
• This clause comes along with the SELECT statement.
• The GROUP BY clause is always follows the WHERE clause and precedes with ORDER BY clause.
• The GROUP BY clause is often used with SQL Aggregate Functions (COUNT(), SUM(), AVG(), MIN(), MAX()).

Syntax

SELECT column-name FROM Table-name
WHERE condition
GROUP BY  column-name
ORDER BY column-name;

Demo Table

Following is the student table in the collegedb database.

mysql> select * from student;
+--------+---------+------+-----------+
| RollNo | Name    | Age  | City      |
+--------+---------+------+-----------+
|      1 | Aruna   |   18 | Chennai   |
|      2 | Varun   |   19 | Bangalore |
|      3 | Ara     |   19 | Kerala    |
|      4 | Markdin |   18 | Mumbai    |
|      5 | Kannan  |   20 | Kerala    |
|      6 | Kanika  |   18 | Chennai   |
|      7 | Jose    |   19 | Kerala    |
+--------+---------+------+-----------+
7 rows in set (0.00 sec)

Example

Following is the example query to count the number of student in the particular age group.

mysql> SELECT COUNT(Rollno),Age FROM student GROUP BY Age;
+---------------+------+
| COUNT(Rollno) | Age  |
+---------------+------+
|             3 |   18 |
|             3 |   19 |
|             1 |   20 |
+---------------+------+
3 rows in set (0.10 sec)

More Example

Following is the example query to list the number of student from differnt city.

mysql> SELECT COUNT(RollNo),City FROM student GROUP BY City;
+---------------+-----------+
| COUNT(RollNo) | City      |
+---------------+-----------+
|             2 | Chennai   |
|             1 | Bangalore |
|             3 | Kerala    |
|             1 | Mumbai    |
+---------------+-----------+
4 rows in set (0.00 sec)

GROUP BY with ORDER BY clause.

mysql> SELECT COUNT(RollNo),City FROM student GROUP BY City ORDER BY City;
+---------------+-----------+
| COUNT(RollNo) | City      |
+---------------+-----------+
|             1 | Bangalore |
|             2 | Chennai   |
|             3 | Kerala    |
|             1 | Mumbai    |
+---------------+-----------+
4 rows in set (0.00 sec)

<<Previous - SQL ORDER BY

Next - SQL HAVIN CLAUSE >>