Results 1 to 12 of 12

Thread: Count dates

  1. #1
    Join Date
    Sep 2009
    Location
    Serbia
    Posts
    17

    Unanswered: Count dates

    Hi guys,
    I need to count distinct dates from table, but output must have number of each date value from table that i select...
    Could you help me please?

    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What is "number of each date value"?

    Would something like this be OK?
    Code:
    select date_column, count(*)
    from your_table
    group by date_column;

  3. #3
    Join Date
    Sep 2009
    Location
    Serbia
    Posts
    17
    Mine column with date values also contains and time values, exampe:

    http://i38.tinypic.com/33nx102.jpg

    so i tried this

    select distinct (auditdatetime), count (*)
    from WIZARD.TWAUDIT
    group
    by auditdatetime;


    AUDITDATE COUNT(*)
    --------- ----------
    10-MAY-05 11
    10-MAY-05 1
    10-MAY-05 1
    10-MAY-05 10
    10-MAY-05 1
    10-MAY-05 1
    10-MAY-05 11
    10-MAY-05 1
    10-MAY-05 10
    10-MAY-05 1
    10-MAY-05 11


    so i want to do count records for each year (2005, 2006 ... 2009)....
    i want to know fro each year how much recorde i have....

  4. #4
    Join Date
    Sep 2009
    Location
    Serbia
    Posts
    17
    Mine column with date values also contains and time values, exampe:

    http://i38.tinypic.com/33nx102.jpg

    so i tried this

    select distinct (auditdatetime), count (*)
    from WIZARD.TWAUDIT
    group
    by auditdatetime;


    AUDITDATE COUNT(*)
    --------- ----------
    10-MAY-05 11
    10-MAY-05 1
    10-MAY-05 1
    10-MAY-05 10
    10-MAY-05 1
    10-MAY-05 1
    10-MAY-05 11
    10-MAY-05 1
    10-MAY-05 10
    10-MAY-05 1
    10-MAY-05 11


    so i want to do count records for each year (2005, 2006 ... 2009)....
    i want to know for each year how much records i have....

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    what is the datatype of AUDITDATE column .
    it seems it is timestamp ... ??

    if it is a timestamp


    select distinct(TO_CHAR(TRUNC(AUDITDATETIME))) ,count(*)
    from WIZARD.TWAUDIT
    group by AUDITDATETIME
    Last edited by mishaalsy; 11-11-09 at 07:39.

  6. #6
    Join Date
    Aug 2009
    Posts
    262
    I think it is a timestamp column

  7. #7
    Join Date
    Sep 2009
    Location
    Serbia
    Posts
    17
    its date datatype.... and your statement dont give what i want :

    (TO_CHAR(TRUNC( COUNT(*)
    --------------- ----------
    01-FEB-07 1
    01-FEB-07 2
    01-FEB-07 3
    01-FEB-07 4

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by mishaalsy View Post
    I think it is a timestamp column
    Does it matter? Even DATE type contains time part to second precision.
    Quote Originally Posted by prepek2000 View Post
    so i want to do count records for each year (2005, 2006 ... 2009)....
    i want to know for each year how much records i have....
    So EXTRACT the desired time part or TRUNC the column to it. Use it in both SELECT and GROUP BY clauses.
    I put the name of suitable SQL functions into uppercase. Their details are described in SQL Reference book, available with many other Oracle documentation books e.g. online on http://tahiti.oracle.com/.
    And get rid of DISTINCT. It has no effect in aggregate (GROUP BY) query.

  9. #9
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    select TRUNC(AUDITDATETIME,'YEAR') ,count(*)
    from WIZARD.TWAUDIT
    group by TRUNC(AUDITDATETIME,'YEAR')
    order by TRUNC(AUDITDATETIME,'YEAR');
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  10. #10
    Join Date
    Sep 2009
    Location
    Serbia
    Posts
    17
    Thanks beilstwh! That was what i looking for!
    Thanks again guys!

  11. #11
    Join Date
    Aug 2009
    Posts
    262
    And get rid of DISTINCT. It has no effect in aggregate (GROUP BY) query.
    Reply With Quote
    My intention is to learn . Can you please give me an example of this in form of query

  12. #12
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What prevents you to do that by yourself?
    Example based on Scott's schema.
    Code:
    SQL> select distinct deptno from emp order by deptno;
    
        DEPTNO
    ----------
            10
            20
            30
    
    SQL> select deptno from emp group by deptno order by deptno;
    
        DEPTNO
    ----------
            10
            20
            30
    
    SQL>

Posting Permissions

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