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
Received on Thu Aug 10 2006 - 15:08:01 CDT
