December 27, 2005 at 6:46 pm
I have 2 tables Family and Billing
The family table holds customer information, and the billing table holds transactions.
Family table: ID Name charges payments balance
Billing table: ID Name charge payment
I need to know how to add all the records in the charge column and update the family charges column.
I have been able to get the charge to goto the family table but it is not adding all of the records from the billing table. Here is what I have right now this will add the value of the first record each time it is run.
Update Family
Set Family.Charges = Billing.Charge + Family.Charges from billing
Where Family.AcctID = Billing.AcctID
I have tried to use SUM(Billing.Charge) but I get an error that I can not use aggregate functions in an UPDATE statement.
I am new at this so Please Help me
Thanks
Josh
December 28, 2005 at 9:02 am
G'day,
create table Family (
ID int,
Name varchar(200),
charges int,
payments int,
balance int
)
go
create table Billing (
ID int,
Name varchar(200),
charge int,
payment int
)
go
UPDATE Family
SET FAMILY.CHARGES = B.Charge
FROM (SELECT ID,SUM(CHARGE) Charge FROM BILLING GROUP BY ID) B
WHERE FAMILY.ID = B.ID
UPDATE Family
SET FAMILY.payments = B.Payment
FROM (SELECT ID,SUM(payment) payment FROM BILLING GROUP BY ID) B
WHERE FAMILY.ID = B.ID
UPDATE Family
SET BALANCE = CHARGES - PAYMENTS
GO
hope this gets you started in the right general direction...
Wayne
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply