一Gourp by和count(*)搭配
#案例1:查询每个位置的部门个数
先根据location_id进行分组,再统计该组的个数
SELECT COUNT(*),location_id
FROM departments
GROUP BY location_id;
#案例2:查询每个工种的员工平均工资
根据job_id进行分组,接着统计以salary为依据的平均工资
SELECT AVG(salary),job_id
FROM employees
GROUP BY job_id;
二count/case搭配分组统计
select count(case when score between 80 and 100 then 1 end)as "80到100",
count(case when score between 60 and 80 then 1 end)as "60到80",
count(case when score between 40 and 60 then 1 end)as "40到60",
count(case when score between 20 and 40 then 1 end)as "20到40",
count(case when score between 0 and 20 then 1 end)as "0到20" from stu_exam;