Count records per month

Question

I'm trying to count the number of appointments in each month for the year of 2014, where the date formatting is like '22-JAN-14' I've tried variations of these two codes so far and both come up with "YEAR": invalid identifier.

CODE 1

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

and CODE 2

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

Any help is appreciated.

Answer 1

Use the ExtractReference

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);

To have a month name rather than number

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);

Answer 2

Data by month and year, sorted by date from oldest to newest:

SELECT
    EXTRACT(year FROM dateofappointment) "Year",
    EXTRACT(month FROM dateofappointment) "Month",
    count(*) 
FROM
    appointment 
GROUP BY
    EXTRACT(year FROM dateofappointment),
    EXTRACT(month FROM dateofappointment) 
ORDER BY
    1,
    2;