GROUP BY on calculated field

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,

Matthew Burgess

Is it possible to use the following field?

to_char(timestamp, 'DD-MON-YY')

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
FROM STATISTICS
GROUP BY TO_CHAR(STAT_TIMESTAMP,'YYYYMMDD');

Thanks,

Matt

Small modification. Sorry if this seems pedantic.

select TO_CHAR(STAT_TIMESTAMP,'YYYYMMDD'), count(*)
from statistics
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,

Matt

Could you post a few records of a printout of the desired solution? I am not sure that I understand the problem clearly.

Thanks

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.

Matt Burgess

select trunc(STAT_TIMESTAMP) "Day", count(*) "No. Stats"
from RACE_STATISTICS
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'),
STAT_DTO_ID,
STAT_I1,
STAT_I2
from tralala
where tralalalala;

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.

Good luck.

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,

Matt

select trunc(STAT_TIMESTAMP) "Day", count(*) "No. Stats"
from RACE_STATISTICS
group by trunc(STAT_TIMESTAMP)
/


does not work? it works for me :o

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

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]