What does group by do exactly ?

Question

From an example taken from here , I'm trying to understand what does GROUP BY do exactly :

Given this employee table :

+-------+----------+--------+------------+
| Empid | Empname  | Salary | DOB        |
+-------+----------+--------+------------+
| 1     | Habib    | 2014   | 2004-12-02 |
| 2     | Karan    | 4021   | 2003-04-11 |
| 3     | Samia    | 22     | 2008-02-23 |
| 4     | Hui Ling | 25     | 2008-10-15 |
| 5     | Yumie    | 29     | 1999-01-26 |
+-------+----------+--------+------------+

After executing mysql> select * from employee group by empname;

We get :

+-------+----------+--------+------------+
| Empid | Empname  | Salary | DOB        |
+-------+----------+--------+------------+
| 1     | Habib    | 2014   | 2004-12-02 |
| 4     | Hui Ling | 25     | 2008-10-15 |
| 2     | Karan    | 4021   | 2003-04-11 |
| 3     | Samia    | 22     | 2008-02-23 |
| 5     | Yumie    | 29     | 1999-01-26 |
+-------+----------+--------+------------+

So , does that mean that GROUP BY just sorts a table by key ?

Thanks

Answer 1

GROUP BY enables summaries. Specifically, it controls the use of summary functions like COUNT(), SUM(), AVG(), MIN(), MAX() etc. There isn't much to summarize in your example.

But, suppose you had a Deptname column. Then you could issue this query and get the average salary by Deptname.

SELECT AVG(Salary) Average,
       Deptname
  FROM Employee
 GROUP BY Deptname
 ORDER BY Deptname

If you want your result set put in a certain order, use ORDER BY.