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

Thread: GROUP BY on calculated field

Hybrid View

  1. #1
    Join Date
    Sep 2000
    Posts
    26
    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

  2. #2
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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?

    David Knight
    OCP DBA 8i, 9i, 10g

  3. #3
    Join Date
    Sep 2000
    Posts
    26
    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

  4. #4
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.

    David Knight
    OCP DBA 8i, 9i, 10g

  5. #5
    Join Date
    Sep 2000
    Posts
    26
    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

  6. #6
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    Could you post a few records of a printout of the desired solution? I am not sure that I understand the problem clearly.

    Thanks
    David Knight
    OCP DBA 8i, 9i, 10g

  7. #7
    Join Date
    Sep 2000
    Posts
    26
    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

  8. #8
    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)
    /

    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)

  9. #9
    Join Date
    Oct 2000
    Location
    Germany
    Posts
    1,185
    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.
    David Knight
    OCP DBA 8i, 9i, 10g

  10. #10
    Join Date
    Sep 2000
    Posts
    26
    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

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