Calculate total and move to new Table

• Josh Quisling

  SSC Enthusiast

  Points: 179

  More actions

  December 27, 2005 at 6:46 pm

  #364495

  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

   

   

• Wayne Lawton

  Hall of Fame

  Points: 3532

  More actions

  December 28, 2005 at 9:02 am

  #611698

  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