MySql Group By Clause

MySql Group By Clause

MySql GROUP BY is used to group the rows based on particular columns and mysql aggregate functions can be used for each groups.

GROUP By Syntax,

SELECT column1, column2, ...,custum_func(columnN)
FROM table-name
GROUP BY <columnN>

mysql> select * from tblemployee;
+--------+----------+---------+--------+
| emp_id | emp_name | dept_id | salary |
+--------+----------+---------+--------+
|      1 | emp1     |      10 |  10000 |
|      2 | emp2     |      11 |  20000 |
|      3 | emp3     |      10 |  15000 |
|      4 | emp4     |      11 |  12000 |
+--------+----------+---------+--------+
4 rows in set (0.00 sec)

mysql> select dept_id, max(salary) 
       from tblemployee group by dept_id;
+---------+-------------+
| dept_id | max(salary) |
+---------+-------------+
|      10 |       15000 |
|      11 |       20000 |
+---------+-------------+
2 rows in set (0.01 sec)

mysql>

Let us see how to group the rows based on multiple tables

There can be no employees in some department, so we need to use left outer join to join tbldepartment and tblemployee tables.

Below MySql query is used to display the highest salary for each department name.

mysql> select dept_name, max(salary) 
from tbldepartment d left outer join tblemployee e 
on d.dept_id=e.dept_id group by dept_name;
+-----------+-------------+
| dept_name | max(salary) |
+-----------+-------------+
| IT        |       15000 |
| Helpdesk  |       20000 |
| People    |        NULL |
+-----------+-------------+
3 rows in set (0.37 sec)

mysql>

« Previous Next »