獲取行組的聚合結果

根據特定列值計算行數:

SELECT category, COUNT(*) AS item_count
FROM item
GROUP BY category;

按部門獲得平均收入:

SELECT department, AVG(income)
FROM employees
GROUP BY department;

重要的是隻選擇 GROUP BY 子句中指定的列或與聚合函式一起使用

WHERE 子句也可以和 GROUP BY 一起使用,但 WHERE 任何分組完成之前過濾掉記錄 :

SELECT department, AVG(income)
FROM employees
WHERE department <> 'ACCOUNTING'
GROUP BY department;

如果你需要在分組完成後過濾結果,例如,僅檢視平均收入大於 1000 的部門,則需要使用 HAVING 子句:

SELECT department, AVG(income)
FROM employees
WHERE department <> 'ACCOUNTING'
GROUP BY department
HAVING avg(income) > 1000;