I have a simple table:
CREATE TABLE People(
People_ID NUMBER(7),
Birth_date DATE
);
I want to know how many people were born in every month in every year:
SELECT EXTRACT(year FROM Birth_date) year, EXTRACT(month FROM Birth_date) month, COUNT(*)
FROM People
GROUP BY EXTRACT(year FROM Birth_date), EXTRACT(month FROM Birth_date);
or
SELECT TO_CHAR(Birth_date, 'YYYY') year, TO_CHAR(Birth_date, 'MM') month, COUNT(*)
FROM People
GROUP BY TO_CHAR(Birth_date, 'YYYY'), TO_CHAR(Birth_date, 'MM');
or
SELECT TO_CHAR(Birth_date, 'YYYY MM'), COUNT(*)
FROM People
GROUP BY TO_CHAR(Birth_date, 'YYYY MM');
The selects work but I have got the warning "SELECT list inconsistent with GROUP BY".How can I correct these selects?
The selects below do not have the warning, but they are not satisfying me.
SELECT year, month, COUNT(*)
FROM (
SELECT EXTRACT(year FROM Birth_date) year, EXTRACT(month FROM Birth_date) month
FROM People
) GROUP BY year, month;
or
SELECT year, month, COUNT(*)
FROM(
SELECT TO_CHAR(Birth_date, 'YYYY') year, TO_CHAR(Birth_date, 'MM') month
FROM People)
GROUP BY year, month;