MySQL Group By Clause
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.