select trunc(STAT_TIMESTAMP) "Day", count(*) "No. Stats"
group by trunc(STAT_TIMESTAMP)
does not work? it works for me
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.
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
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
count(*) "No. Stats"
group by trunc(STAT_TIMESTAMP), STAT_DTO_ID,
to use function based index you have to add this to initSID.ora
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]
Click Here to Expand Forum to Full Width