MySQL Group By Clause is used to divide the whole table into small group according to the given column(s). The syntax of the group by clause is as follow

Syntax

SELECT column1, column2, column3........
FROM  tableName
GROUP BY column1

Sample Database to Demonstrate Group By Clause

+-------+---------+--------+-------+-------+---------+--------+
| admno | name    | stream | marks | grade | fees    | gender |
+-------+---------+--------+-------+-------+---------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |
| 112   | vipul   | comm   | 88.32 | A     | 1650.75 | M      |
+-------+---------+--------+-------+-------+---------+--------+

Example of Group By clause on Stream

mysql> select admno,name,stream from student group by stream;
+-------+--------+--------+
| admno | name   | stream |
+-------+--------+--------+
| 103   | mohit  | bio    |
| 105   | unnati | comm   |
| 104   | mannat | human  |
| 101   | rakesh | sci    |
+-------+--------+--------+
4 rows in set (0.00 sec)

MOST important Point to be Noted Here

Here you will be surprised to see the output of the above command. Basically when a group by clause is applied on stream than the above table has been divided into four sub-tables or groups and the above select command is only showing the first row of each such sub-tables.

Actual state of the above Table is something like this

Stream Table- Bio
+-------+---------+--------+-------+-------+---------+--------+
| admno | name    | stream | marks | grade | fees    | gender |
+-------+---------+--------+-------+-------+---------+--------+
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |
+-------+---------+--------+-------+-------+---------+--------+

Stream-Table: Comm
+-------+---------+--------+-------+-------+---------+--------+
| admno | name    | stream | marks | grade | fees    | gender |
+-------+---------+--------+-------+-------+---------+--------+
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |
| 112   | vipul   | comm   | 88.32 | A     | 1650.75 | M      |
+-------+---------+--------+-------+-------+---------+--------+

Stream-Table: Human
+-------+--------+--------+-------+-------+---------+--------+
| admno | name   | stream | marks | grade | fees    | gender |
+-------+--------+--------+-------+-------+---------+--------+
| 104   | mannat | human  | 67.50 | A     | 1420.15 | F      |
+-------+--------+--------+-------+-------+---------+--------+

Stream-Table: sci
+-------+--------+--------+-------+-------+---------+--------+
| admno | name   | stream | marks | grade | fees    | gender |
+-------+--------+--------+-------+-------+---------+--------+
| 101   | rakesh | sci    | 96.56 | A     | 1850.35 | F      |
| 102   | udit   | sci    | 56.50 | B     | 1850.35 | M      |
| 106   | nikunj | sci    | 79.50 | A     | 1850.35 | M      |
+-------+--------+--------+-------+-------+---------+--------+

So you can also say, Group by clause is used to divide the table into sub-table but when the group by function works then the calculation is performed on all these sub-tables and then this result is again combined.

Example

mysql>SELECT stream, count(stream) FROM student
GROUP BY stream;
+--------+---------------+
| stream | count(stream) |
+--------+---------------+
| bio    |             2 |
| comm   |             6 |
| human  |             1 |
| sci    |             3 |
+--------+---------------+

The above code displays the name of each sub-table and then finds out the total number of rows in each table then the whole data is combined and displayed on the screen.