MySQL Tutorial
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>
MySQL Tutorial
Privacy Policy | Copyright2020 - All Rights Reserved. | Contact us
| Report website issues in Github
| Facebook page
| Google+ page