我有以下查询:
SELECT
COUNT(*) AS "Number of Employees",
EXTRACT(MONTH FROM hire_date) AS "Month Hired",
AVG(salary)
FROM
hr.employees;
它给我的错误:ORA-00937: not a single-group group function
。我尝试了其他人提供的其他资源和答案,但是在我的情况下它们不起作用。
我该如何解决?
添加一个GROUP BY
:
SELECT COUNT(*) AS "Number of Employees",
EXTRACT(MONTH FROM hire_date) AS "Month Hired",
AVG(salary)
FROM hr.employees
GROUP BY EXTRACT(MONTH FROM hire_date);
您可能只对录用月份感兴趣可能是有原因的。但是,通常情况下,您也需要年份。在Oracle中,通常可以使用TO_CHAR()
以下命令完成此操作:
SELECT TO_CHAR(hire_date, 'YYYY-MM') as month_hired,
COUNT(*) AS num_empoyees,
AVG(salary)
FROM hr.employees
GROUP BY TO_CHAR(hire_date, 'YYYY-MM')
ORDER BY month_hired;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句