单列分组

需求：查询各个部门的平均工资，最高工资

SELECT department_id,AVG(salary),SUM(salary)
FROM employees
GROUP BY department_id;

多列分组

需求：查询各个department_id,job_id的平均工资

#方式1：
SELECT department_id,job_id,AVG(salary)
FROM employees
GROUP BY  department_id,job_id;
#方式2：
SELECT job_id,department_id,AVG(salary)
FROM employees
GROUP BY job_id,department_id;

• 结论1：SELECT中出现的非组函数的字段必须声明在GROUP BY 中。反之，GROUP BY中声明的字段可以不出现在SELECT中。
• 结论2：GROUP BY 声明在FROM后面、WHERE后面，ORDER BY 前面、LIMIT前面
• 结论3：MySQL中GROUP BY中使用WITH ROLLUP

WITH ROLLUP：在group分组字段的基础上再进行统计数据。

SELECT department_id,AVG(salary)
FROM employees
GROUP BY department_id WITH ROLLUP;
#需求：查询各个部门的平均工资，按照平均工资升序排列
SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id
ORDER BY avg_sal ASC;

说明：当使用WITH ROLLUP时，不能同时使用ORDER BY子句进行结果排序，即ROLLUP和ORDER BY是互相排斥的。

错误写法：

SELECT department_id,AVG(salary) avg_sal
FROM employees
GROUP BY department_id WITH ROLLUP
ORDER BY avg_sal ASC;