Home » RDBMS Server » Server Administration » Grouping or summing an aggregate function
Grouping or summing an aggregate function [message #370547] Fri, 17 December 1999 10:10 Go to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Could someone tell me how I can use one function on another one. Specifically speaking I have an SQL statement that subtracts two time/date fields to get the time difference between the two then ROUNDS the result. There are about 250 Organizations (ORG.ORG_NAME), each with multiple entries. Ultimately I would like to list each organization with a total or sum of their times. Any help would be greatly appreciated.
Here's what I have so far:

SELECT ORG.ORG_NAME,
ROUND(((ACCT_BACKUP.CREATED - ACCT_BACKUP.STARTED) * 1440),2)

FROM ORG, ACCT_BACKUP,USR

WHERE ACCT_BACKUP.LOGIN_NAME = USR.LOGIN_NAME AND
ACCT_BACKUP.CREATED >= '01-NOV-99' AND ACCT_BACKUP.CREATED <= 30-NOV-99 AND
ORG.ORG_ID = USR.USR_ORG_ID AND
ACCT_BACKUP.BILLINGID IS NOT NULL

ORDER BY ORG.ORG_NAME
Re: Grouping or summing an aggregate function [message #370556 is a reply to message #370547] Mon, 20 December 1999 06:40 Go to previous messageGo to next message
Paul
Messages: 164
Registered: April 1999
Senior Member
Chris,
HMG's solution should work, but you may want to reverse the order of the sum and round operations -
ROUND(SUM(expression))
This is normal practice, sum first, then round to avoid compounding rounding errors.
Hope this helps,
Paul
Thanks HMG [message #370558 is a reply to message #370547] Mon, 20 December 1999 09:11 Go to previous messageGo to next message
Chris
Messages: 128
Registered: November 1998
Senior Member
Thank You for your help.

Take Care
Chris
Thanks Paul [message #370559 is a reply to message #370547] Mon, 20 December 1999 09:12 Go to previous message
Chris
Messages: 128
Registered: November 1998
Senior Member
Thank You for the help.

Take Care
Chris
Previous Topic: Comparing dates and times
Next Topic: Re: Expression limit in where clause
Goto Forum:
  


Current Time: Wed Oct 28 07:05:52 CDT 2020