Home » RDBMS Server » Server Administration » not a single-group group function
not a single-group group function [message #370660] Mon, 17 January 2000 12:46 Go to next message
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 Go to previous messageGo to next message
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
Re: not a single-group group function [message #370663 is a reply to message #370660] Mon, 17 January 2000 21:20 Go to previous message
Daniel G.
Messages: 1
Registered: January 2000
Junior Member
Add the "GROUP BY" clause to the end of your query. in the clause specify all non-scalar variables. i.e. usr_id and "pricing_plan_title"
Previous Topic: Excluding saturday & Sunday
Next Topic: Sending E-mail from PL/SQL
Goto Forum:
  


Current Time: Sat Oct 31 21:02:50 CDT 2020