Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2009
    Posts
    7

    Unanswered: Can't have aggregate function in expression

    Can't have aggregate function in expression is what I am getting when I try this:
    =Avg & Format(Avg (([FFF]-(Avg([FFF]/3600)*3600))/60),"00") & ":" & Format(([FFF] Mod 60),"00")

    However if I change it up to:
    =Avg ([FFF]/3600)"00") & ":" & Format(Avg (([FFF]-(Avg([FFF]/3600)*3600))/60),"00") & ":" & Format(([FFF] Mod 60),"00")

    It removes the issue but does not run it properly.
    The result I am looking for is to take a time stamp of minutes (n.xxxx) to format into h:nn:ss but am having no luck.
    Thanks in advanced...

  2. #2
    Join Date
    May 2009
    Posts
    258
    This should do the trick:
    Code:
    =Format(Int(FFF / 60) & Int(FFF - Int(FFF / 60) * 60) & (FFF - Int(FFF)) * 60, "00:00:00")
    The last part you'll have to figure out if you want to round seconds up or down.

    Regards,

    Ax

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Krvin, What Version of DB2 are you using and what is the operating system?

    What I would do is calculate each part (hour, minute and second) separately and then concatenate the values to the correct format.

    It appears that what you can't do is nest aggregate functions (going by you example).

    I am also not sure why you need the AVG function when doing a time conversion.

    Can you give an example of what your input is (I am guessing n.xxxx is minutes and seconds but I really don't know). If the xxxx is seconds, do you want to Round the value or Truncate?

    After these questions are answered, I might be able to create a working SQL statement.

  4. #4
    Join Date
    Jul 2009
    Posts
    7

    Can't have aggregate function in expression

    I am using IBM DB2 v7.2 on Server 2003.
    The n.xxxx = minutes with it being broke down into decimal. Also where the FFF is pointing to it is set to: FFF: DateDiff("s",[TEST_PERFORMED],[TEST_ACQUIRED])
    This is what I have come up with but still am unable to get a correct result:
    =Avg([FFF])*60=Format("00\:00\:00")

    Any other thoughts?
    Last edited by Krvin; 07-17-09 at 11:01.

Posting Permissions

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