Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Posts
    15

    Unanswered: SUM of Count returned by subquery

    I am a noob at Oracle and I am having a problem creating a proper SQL statement to calculate the sum of how many times the same date is found.

    TABLE LOOKS LIKE:

    ID | CREATE_TIME
    -----------------------------
    1 7/7/2003 1:00AM
    2 7/7/2003 2:00AM
    3 7/7/2003 3:00AM
    4 7/8/2003 6:00AM
    5 7/8/2003 8:00AM
    6 7/9/2003 10:00AM


    If I execute the following query:
    SELECT Count(*) REC_COUNT FROM mytable WHERE TRUNC(CREATE_TIME) BETWEEN TO_DATE('7/7/2003','MM/DD/YYYY') AND TO_DATE('7/7/2003','MM/DD/YYYY')


    I get the following:

    REC_COUNT
    --------------
    6

    But what I want is something like this:

    REC_COUNT | CREATE_TIME
    -----------------------------
    3 7/7/2003 1:00AM
    2 7/8/2003 2:00AM
    1 7/9/2003 3:00AM


    That is a the record count for each day within the date range. and if there is more than one for a given day... to return the sum for that day.


    Thanks in Advance

  2. #2
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Arrow Group By

    Hi
    U need to use group by for such cases.

    SELECT Count(*) REC_COUNT, to_char(create_time,'YYYY/MM/DD')
    FROM mytable WHERE TRUNC(CREATE_TIME) BETWEEN TO_DATE('7/7/2003','MM/DD/YYYY') AND TO_DATE('7/10/2003','MM/DD/YYYY') group by to_char(create_time,'YYYY/MM/DD');

    Thanx and Regards
    Aruneesh

  3. #3
    Join Date
    Aug 2003
    Posts
    15

    Thumbs up

    THANKS aruneeshsalhotr!

    That did the trick!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •