GROUP BY on calculated field - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 2 FirstFirst 12
Results 11 to 13 of 13

Thread: GROUP BY on calculated field

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    select trunc(STAT_TIMESTAMP) "Day", count(*) "No. Stats"
    from RACE_STATISTICS
    group by trunc(STAT_TIMESTAMP)
    /


    does not work? it works for me

  2. #12
    Join Date
    Sep 2000
    Posts
    26
    Yes, thanks Pando that does work in terms of summarizing the data, but as mentioned previously I then need access to the original records that lie beneath that summary. So for example say your code returns the following info:

    DATE | Count(*)
    17/10/1997 | 45
    18/10/1997 | 2

    This would suggest that possibly something went wrong on 17/10/1997 to generate so much info. Therefore I need a way to get to those 45 records in order to see what they were actually recording.

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well then obviously you have to use two queries, I thought you just wanted the summary In one query to get the summary and the description is kind of stupid anyway though, you will see like 45 for all descriptions unless you group again like

    Code:
    select 
    trunc(STAT_TIMESTAMP) "Day", 
    STAT_DTO_ID,
    STAT_I1, 
    STAT_I2,
    count(*) "No. Stats" 
    from RACE_STATISTICS 
    group by trunc(STAT_TIMESTAMP), STAT_DTO_ID,
    STAT_I1, STAT_I2 
    /
    and you have to create function based index if you plan to pass as a parameter to Oracle the timestamp otherwise it will do full table scan
    to use function based index you have to add this to initSID.ora

    QUERY_REWRITE_ENABLED=TRUE
    QUERY_REWRITE_INTEGRITY=TRUSTED

    bounce database
    There is a bug in 8.1.5 trunc cant work with function based indexes, fixed in 8.1.6

    This is about 8i.... not sure other versions

    [Edited by pando on 09-18-2001 at 06:18 AM]

Posting Permissions

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


Click Here to Expand Forum to Full Width