My table has over 1 million records which I want grouped by the day of the TIMESTAMP field. However there are often more than one record with the same TIMESTAMP (e.g there are 4 records with timestamp of "21/10/1997 14:21:30") so when I group purely by TIMESTAMP I still get more than one record per day. I've tried ROUND, TRUNC and TO_CHAR to format the timestamp but the resulting GROUP BY statement fails ("not a valid GROUP BY statement"). It seems as though GROUP BY does not work on calculated fields. If this is the case could someone suggest a workaround? If my assumption is wrong could you give me the correct syntax please.
Thanks in advance,
Is it possible to use the following field?
and select this derived field and also group by this derived field?
You state that you want to group by the day. Are you summarizing by day?
I have tried the following code (maybe this provides a clearer explanation of what I want to achieve - simply getting a count for the number of records per distinct date (but not time) in the timestamp field):
SELECT TO_CHAR(STAT_TIMESTAMP,'YYYYMMDD'), DESCRIPTION
GROUP BY TO_CHAR(STAT_TIMESTAMP,'YYYYMMDD');
Small modification. Sorry if this seems pedantic.
select TO_CHAR(STAT_TIMESTAMP,'YYYYMMDD'), count(*)
group by TO_CHAR(STAT_TIMESTAMP,'YYYYMMDD');
You cannot throw in the Descriptions field unless you are grouping by that field also.
Hope that helps.
OK, thanks. But I do need to get at the data underneath this summary information - is there a way to "drill-down" to get to the actual records for a particular day. FYI I am actually using Excel 97 to connect to the Oracle 7.3.4 DB we have through an ODBC driver. Incidentally I've tried using the GROUP BY ROLLUP command on your previous code to provide a total of the count column and it doesn't work either! Is this only supported in Oracle 8 onwards?
Thanks once again,
Could you post a few records of a printout of the desired solution? I am not sure that I understand the problem clearly.
Assume the following data is present in the RACE_STATISTICS table:
STAT_TIMESTAMP | STAT_DTO_ID | STAT_I1 | STAT_I2
17/10/1997 09:54 | NONAME | ALD_LO | LOGON
17/10/1997 09:54 | NONAME | ALD_LF | LOGOFF
17/10/1997 10:15 | DLONGS | CALL_OF | OFFER
18/10/1998 14:30 | DELLIS | SITE_NO | NOACCES
So, now I would like the following information:
DAY | No. Stats. |
17/10/1997 | 3 |
18/10/1997 | 1 |
Plus I would like to be able to somehow get back to the data listed in the first table by selecting the date on which to query. So for instance if the date field is returned to Excel, I could possibly double click on the cell containg that date and it would return all 4 fields from the first table (TIMESTAMP, DTO_ID, I1 & I2).
Hope this clarifies things for you.
select trunc(STAT_TIMESTAMP) "Day", count(*) "No. Stats"
group by trunc(STAT_TIMESTAMP)
this perfomance wise may be not very good if you want to filter by timestamp adding a where in your query, you may have to create a function based index on trunc(STAT_TIMESTAMP)
Okay. This is not an Oracle solution though.
Select to_char(STAT_TIMESTAMP, 'DD-MON-YY'),
You should receive output similar to what you printed, but without a timestamp.
Import these records into Excel. Then use the option that summarizes the records. I have a German Excel version and the heading is Data, the option is the fourth after Sort, Filter, and Mask. I believe it translates like Summarize.
You should get summary and detail capabilities with this.
I would have done it this way originally, however essentially I need access to all (1million+) records from the table. Because Excel only supports ~64k records I can't import all of the data to then summarise. Therefore I was trying to get Oracle to summarize it for me first, therefore returning much fewer rows, but hopefully still allowing access to the underlying records. The only way I can see of doing this now is by having 2 queries - the first groups by date. The other is a parameter query based on a user selected date whcih then returns the details of the statistics generated on the given date.
Many thanks for your efforts,