Run a Count on a portion of a Date

Hey all,

I am trying to run a query that will count the number of each occurrence of a combined month and day.

Birthdays are formatted as: 1947-07-26 00:00:00

I need to query everyone in the system who share the same month & day, but not year & time. For example:

1927-07-26 00:00:00
1927-07-26 00:00:00
1987-07-26 00:00:00
1965-07-26 00:00:00
1981-03-14 00:00:00

would give me a Count of 4 for 7/26 and a Count of 1 for 3/14

Error: ORA-00904: "DAY": invalid identifier
(State:S0022, Native Code: 388)

DBMS OUTPUT
-----------
”�

please allow me to point out that you posted an oracle problem in the mysql forum

i shall now move your thread to the oracle forum

you could also, you know, just look up in the oracle manual the equivalent to the MONTH() and DAY() functions that are specific to oracle

This is one way to give you the count by month/day:

Code:

select to_char( birth_date, 'mm-dd' ) as birth_day, count(*)
 from  schema.table
group by to_char( birth_date, 'mm-dd' )

And if you wanted to only count people born in July(07)/1977:

Code:

select to_char( birth_date, 'mm-dd' ) as birth_day, count(*)
 from  schema.table
where  birth_date between to_date( '07-01-1977','mm-dd-yyyy' )
 and                      to_date( '07-31-1977','mm-dd-yyyy' )
group by to_char( birth_date, 'mm-dd' )