| not a single-group group function [message #370660] |
Mon, 17 January 2000 12:46  |
Chris
Messages: 128
Registered: November 1998
|
Senior Member |
|
|
Hi there,
Could someone tell me how to sum a field I have without getting the 'not a single-group group function' error?
This is the SQL I have. As you can imagine the query works beautifully without the sum. Basically I want to total up the usage of all users (ACCT_BACKUP.DELTA) and group that usage according to specific groups (A.VALUE AS 'PRICING_PLAN_TITLE')
Thank You
Chris
SELECT USR.USR_ID, ROUND(SUM(ACCT_BACKUP.DELTA)/3600,2), A.VALUE AS PRICING_PLAN_NUMBER, B.VALUE AS PRICING_PLAN_TITLE
FROM USR, ACCT_BACKUP, SVC_DATA A, SVC_DATA B, SVC_DATA C
WHERE (USR.BILLINGID = ACCT_BACKUP.BILLINGID)
AND (ACCT_BACKUP.TYPE= 2)
AND (ACCT_BACKUP.CREATED >= '01-JAN-2000' AND ACCT_BACKUP.CREATED <= ) <BR 05-JAN-2000>AND (A.ATTR_NAME = 'PRICING_PLAN_NUMBER' AND A.ENTITY_ID = 50001042)
AND (B.ATTR_NAME = 'PRICING_PLAN_TITLE' AND B.ENTITY_ID = 50001042 AND B.SUBSCRIPT = A.SUBSCRIPT)
AND (C.ATTR_NAME = 'CHOSEN_PRICING_PLAN' AND (C.ENTITY_ID = USR.USR_ID) AND (C.VALUE = A.VALUE))
|
|
|
|
| Re: not a single-group group function [message #370661 is a reply to message #370660] |
Mon, 17 January 2000 14:17  |
Paul
Messages: 164
Registered: April 1999
|
Senior Member |
|
|
Chris,
Since the query works fine without the SUM(), would it be acceptable to run this as a SQL report like this:
set pagesize 54
set linesize 80
column USER_ID heading 'User'
column USAGE heading 'Usage' format 99999.90
column PRICING_PLAN_TITLE heading 'Pricing Plan'
column PRICING_PLAN_NUMBER heading 'Plan Number'
break on Report on PRICING_PLAN_TITLE skip 1
compute sum of USAGE on PRICING_PLAN_TITLE Report
SELECT USR.USR_ID AS USER_ID,
ACCT_BACKUP.DELTA / 3600 AS USAGE,
A.VALUE AS PRICING_PLAN_NUMBER,
B.VALUE AS PRICING_PLAN_TITLE
FROM USR,
ACCT_BACKUP,
SVC_DATA A,
SVC_DATA B,
SVC_DATA C
WHERE (USR.BILLINGID = ACCT_BACKUP.BILLINGID)
AND (ACCT_BACKUP.TYPE= 2)
AND (ACCT_BACKUP.CREATED >= '01-JAN-2000' AND ACCT_BACKUP.CREATED <=
'05-JAN-2000')
AND (A.ATTR_NAME = 'PRICING_PLAN_NUMBER' AND A.ENTITY_ID = 50001042)
AND (B.ATTR_NAME = 'PRICING_PLAN_TITLE' AND B.ENTITY_ID = 50001042
AND B.SUBSCRIPT = A.SUBSCRIPT)
AND (C.ATTR_NAME = 'CHOSEN_PRICING_PLAN' AND (C.ENTITY_ID =
USR.USR_ID)
AND (C.VALUE = A.VALUE));
You can add titles and footers and format the rest of the columns as you wish, if you wish. Might be worth a try.
Paul
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
