#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0

    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
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Code:
    SELECT MONTH(thedate)
         , DAY(thedate)
         , COUNT(*)
      FROM daTable
    GROUP
        BY MONTH(thedate)
         , DAY(thedate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2012
    Posts
    2
    Rep Power
    0
    Error: ORA-00904: "DAY": invalid identifier
    (State:S0022, Native Code: 388)

    DBMS OUTPUT
    -----------
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by Prolific
    Error: ORA-00904
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    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' )
  10. #6
  11. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2012
    Location
    spaceBAR Central
    Posts
    229
    Rep Power
    42
    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' )

IMN logo majestic logo threadwatch logo seochat tools logo