sql - 每月记录数

Question

我正在尝试计算 2014 年每个月的约会数量，其中日期格式类似于“22-JAN-14”到目前为止，我已经尝试过这两个代码的变体，并且都得出了“YEAR”:无效标识符。

代码1

SELECT count(*), MONTH(dateofappointment) as month, YEAR(dateofappointment) as year
FROM appointment
GROUP BY month
HAVING YEAR(dateofappointment) ='14'
ORDER BY month;

和代码 2

select count(*)
from appointment
group by month(dateofappointment)
having year(dateofappointment) = '14';

感谢任何帮助。

Answer 1

使用摘录引用

SELECT EXTRACT(month FROM dateofappointment) "Month", count(*)
FROM appointment
WHERE EXTRACT(YEAR FROM dateofappointment) = '2014'
GROUP BY EXTRACT(month FROM dateofappointment)
ORDER BY EXTRACT(month FROM dateofappointment);

有月份名称而不是数字

SELECT TO_CHAR(TO_DATE(EXTRACT(month FROM dateofappointment), 'MM'), 'MONTH') "Month", count(*)
FROM appointment
WHERE EXTRACT(YEAR FROM dateofappointment) = '2014'
GROUP BY EXTRACT(month FROM dateofappointment)
ORDER BY EXTRACT(month FROM dateofappointment);