RE: Query Question

How about this:  

select acc_sum.customer_name, acc_sum.total_amount, det.detail_item from (select account_no, customer_name, sum(amount) total_amount from accounts) acc_sum,

        details det
where acc_sum.account_no = det.account_no;    

-- 
Mark J. Bobak 
Senior Oracle Architect 
ProQuest Information & Learning 

Ours is the age that is proud of machines that can think and suspicious
of men who try to.  --H. Mumford Jones, 1892-1980

 

________________________________

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hostetter, Jay M
Sent: Thursday, August 10, 2006 4:08 PM
To: Oracle-L_at_FreeLists.org
Subject: Query Question


I know there has to be a simple way to do this, but my brain can't put
it togther this afternoon.  I have a parent table (accounts) and a child
table (details).  I am creating a view that ties the two tables
together.  I would like a column in this view that shows a summary of a
column in the parent table - but I want this summary to be for each
distinct parent record - not the child records.
 
Here is my view:
 
create view test_view 


(customer_name,

amount,
detail_item)
as


(select customer_name,

sum(amount),
detail_item
from accounts a,
       details d
where a.account_no=d.account_no
group by customer_name;
 
Sample records from Accounts (Customer_name, Account_no, Amount):
Fred, 12345, 10
Fred, 45678, 15
 
Sample records from Details (Account_No, Detail_item, Description):
12345, Telephone, yaddayadda
12345, Call Forwarding, yaddayadda
45678, Cell Phone, yaddayadda
 
I would like my view to return (note the 25, which is a sum of the two
disting Amount values from Accounts):
Fred, 25, Telephone
Fred, 25, Call Forwarding
Fred, 25, Cell Phone
 
Instead, I am getting (note the 35, which is the sum of the amount
field, when joined with details, because the value 10 is duplicated due
to 2 child records for acocount 12345):
Fred, 35, Telephone
Fred, 35, Call Forwarding
Fred, 35, Cell Phone
 
In my view, the sum(amount) is summing for each row in the view.  I want
it to sum for each distinct account number.  I know that I can use
sum(distinct amount), but there is no guarantee that the amounts will
always be unique - the PK is the account number.
 
Thank you,
Jay
 
 
 





**DISCLAIMER
This e-mail message and any files transmitted with it are intended for
the use of the individual or entity to which they are addressed and may
contain information that is privileged, proprietary and confidential. If
you are not the intended recipient, you may not use, copy or disclose to
anyone the message or any information contained in the message. If you
have received this communication in error, please notify the sender and
delete this e-mail message. The contents do not represent the opinion of
D&E except to the extent that it relates to their official business.	

--
http://www.freelists.org/webpage/oracle-l