Home » SQL & PL/SQL » SQL & PL/SQL » Unions + Grouping
Unions + Grouping [message #307736] Wed, 19 March 2008 14:19 Go to next message
fmrock
Messages: 45
Registered: December 2006
Member
I am working on the following query, and hopefully some one can point me in the direction i am going wrong.

I am trying to sum up two queries that I have unioned together.

No matter what i try i get an error on the outer grouping.

Thanks for any help you may have.

Select 
	Group_Num, 
	Sum(Num_Accounts) "Num_Accounts", 
	Sum(Num_of_99053) "Num_of_99053",
	Sum(Charge_Amount) "Total_Of_Charges",
	Sum(Payment_Count) "Num_of_Payments",
	Sum(Payment_Amount) "Total_of_Payments"
from (
SELECT 
	Account.ACCTCPCODE "Group_Num", 
	count(Account.ACCTCODE) "Num_Accounts", 
	count(Charges.PRCODE) "Num_of_99053", 
	sum(Charges.PRAMOUNT) "Charge_Amount",
	0 "Payment_Count", 
	0 "Payment_Amount"
FROM   
	Account Account 
	JOIN Charges Charges ON ((Account.ACCTCPCODE=Charges.CPCODE) AND (Account.ACCTCODE=Charges.ACCOUNT) AND (Charges.TYPE='C') AND (Charges.SPLITFLAG IS  NULL)  AND (Charges.PRCODE='99053'))
WHERE 
	(Account.ACCTDEACTIVE IS  NULL)
	AND to_date(Account.ACCTLOGDATE,'j')>=TO_DATE ('01-09-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
	AND (Account.ACCTCPCODE = '155003')
GROUP BY
	Account.ACCTCPCODE
UNION ALL
SELECT 
	Account.ACCTCPCODE "Group_Num", 
	0 "Num_Accounts", 
	0 "Num_of_99053", 
	0 "Charge_Amount",
	count(Charges_1.PRCODE) "Payment_Count", 
	sum(Charges_1.PRAMOUNT) "Payment_Amount"
FROM   
	Account Account 
	JOIN Charges Charges ON ((Account.ACCTCPCODE=Charges.CPCODE) AND (Account.ACCTCODE=Charges.ACCOUNT) AND (Charges.TYPE='C') AND (Charges.SPLITFLAG IS  NULL)  AND (Charges.PRCODE='99053'))
	JOIN Charges Charges_1 ON ((Charges.CPCODE=Charges_1.CPCODE) AND (Charges.ACCOUNT=Charges_1.ACCOUNT) AND (Charges.SEQNO=Charges_1.APPLYSEQNO) AND (Charges_1.TYPE='P') AND (Charges_1.SPLITFLAG IS  NULL))
WHERE 
	(Account.ACCTDEACTIVE IS  NULL)
	AND to_date(Account.ACCTLOGDATE,'j')>=TO_DATE ('01-09-2007 00:00:00', 'DD-MM-YYYY HH24:MI:SS')
	AND (Account.ACCTCPCODE = '155003')
GROUP BY
	Account.ACCTCPCODE
) U
GROUP BY Group_Num


Re: Unions + Grouping [message #307737 is a reply to message #307736] Wed, 19 March 2008 14:21 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
The error i am getting is

ERROR at line 44:
ORA-00904: "GROUP_NUM": invalid identifier

and line 44 is the outer group by
Re: Unions + Grouping [message #307739 is a reply to message #307737] Wed, 19 March 2008 14:41 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
  1  with t
  2  as
  3  (
  4  select 1 no from dual
  5  union all
  6  select 2 from dual
  7  )
  8* select * from t
SQL> /

        NO
----------
         1
         2


  1  with t
  2  as
  3  (
  4  select 1 no from dual
  5  union all
  6  select 2 from dual
  7  )
  8  select no_val from
  9  (
 10* select no "no_val"  from t)
SQL> select no_val from
       *
ERROR at line 8:
ORA-00904: "NO_VAL": invalid identifier


  1  with t
  2  as
  3  (
  4  select 1 no from dual
  5  union all
  6  select 2 from dual
  7  )
  8  select "no_val" from
  9  (
 10* select no "no_val"  from t)
SQL> /

    no_val
----------
         1
         2


  1  with t
  2  as
  3  (
  4  select 1 no from dual
  5  union all
  6  select 2 from dual
  7  )
  8  select no_val from
  9  (
 10* select no no_val  from t)
SQL> /

    NO_VAL
----------
         1
         2

Regards

Raj
Re: Unions + Grouping [message #307744 is a reply to message #307737] Wed, 19 March 2008 15:07 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8622
Registered: November 2002
Location: California, USA
Senior Member
The following just adds a little more to what S.Rajarm is showing. Any time that you enclose a column alias in double quotes, you make it case sensitive, so anywhere that it is referenced, you must also use double quotes to match the case. Please see the additional demo below.

-- reproduction of problem:
SCOTT@orcl_11g> select Group_Num, sum (Num_Accounts) "Num_Accounts"
  2  from   (select dummy "Group_Num", count (*) "Num_Accounts" from dual group by dummy
  3  	     union all
  4  	     select dummy "Group_Num", count (*) "Num_Accounts" from dual group by dummy)
  5  group  by Group_Num
  6  /
group  by Group_Num
          *
ERROR at line 5:
ORA-00904: "GROUP_NUM": invalid identifier


-- either don't use quotes in the inner select:
SCOTT@orcl_11g> select Group_Num, sum (Num_Accounts) "Num_Accounts"
  2  from   (select dummy Group_Num, count (*) Num_Accounts from dual group by dummy
  3  	     union all
  4  	     select dummy Group_Num, count (*) Num_Accounts from dual group by dummy)
  5  group  by Group_Num
  6  /

G Num_Accounts
- ------------
X            2


-- or use quotes in the outer select and group by to reference columns in the inner select:
SCOTT@orcl_11g> select "Group_Num", sum ("Num_Accounts") "Num_Accounts"
  2  from   (select dummy "Group_Num", count (*) "Num_Accounts" from dual group by dummy
  3  	     union all
  4  	     select dummy "Group_Num", count (*) "Num_Accounts" from dual group by dummy)
  5  group  by "Group_Num"
  6  /

G Num_Accounts
- ------------
X            2

SCOTT@orcl_11g> 

Re: Unions + Grouping [message #307748 is a reply to message #307744] Wed, 19 March 2008 15:38 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Thanks barbara. I completely missed that point. Good one.

Regards

Raj
Re: Unions + Grouping [message #307749 is a reply to message #307736] Wed, 19 March 2008 15:40 Go to previous messageGo to next message
fmrock
Messages: 45
Registered: December 2006
Member
I will try this in the morning. Thanks for your help
Re: Unions + Grouping [message #307924 is a reply to message #307736] Thu, 20 March 2008 06:20 Go to previous message
fmrock
Messages: 45
Registered: December 2006
Member
Worked by taking off the Double quotes.

Thanks for your help
Previous Topic: Disable a Trigger by another trigger
Next Topic: how to reduce the time for Package to execute
Goto Forum:
  


Current Time: Sat Dec 03 03:54:15 CST 2016

Total time taken to generate the page: 0.14457 seconds