SQLTeam.com | Weblogs | Forums

Applying Sum of several columns in Table1 to Fields in Table2



Have tried several things.. but must be 'lost on the concept...'
Here are the two tables:
Table = Task
Task.Rec = XXXXXX
Task.ClientRec = XXX
TotalDetailExpense money
TotalPaidExpense money

Table = TaskDtls
TaskDtls.Rec = XXXXXX
TaskDtls.ClientRec = XXX
TaskDtls.iSeq int
DetailExp money
PaidExp money

There are many detail records associated with the main task record.
Task.Rec ='AAA'
Task.ClientRec = 'A1a1'

TaskDtls.Rec TaskDtls.ClientRec TaskDtls.iSeq TaskDtls.DetailExp TaskDtls.PaidExp
'AAA' A1a1 1 2.00 1.00
'AAA' A1a1 2 4.00 2.00
'AAA' A1a1 3 9.00 3.00

So at the end of the query the
Task.TotalDetailExpense =sum(TskDtls.DetailExp)
Task.TotalPaidExpense = sum(TskDtls.PaidExp)

I tried something similar 
SET Task.TotalDetailExpense = S1.ExpAmt,  TotalPaidExpense = S1.PdAmt
FROM  Task
INNER JOIN (SELECT SUM(DetailExp) as ExpAmt, SUM(.PaidExp) as PdAmt
FROM TaskDtls   WHERE   TaskDtl.Rec ='AAA' AND TaskDtl.ClientRec = 'A1a1'
But that didn't work.. tried other combinations ....
One MAIN point, is the update has to be specific a certain set
of record (i.e. TaskDtl.Rec ='AAA' AND TaskDtl.ClientRec = 'A1a1')
I don't want to have the whole table updated.
Thanks in advance for your advice / directions


This might get you started:

update t
   set t.TotalDetailExpense=s.ExpAmt
  from Task as t
       inner join (select Rec
                         ,sum(DetailExp) as ExpAmt
                         ,sum(PaidExp) as PdAmt
                     from TaskDtls
                    where Rec='AAA'
                      and ClientRec='A1a1'
                    group by Rec
                  ) as s
               on s.Rec=t.Rec
              and s.ClientRec=t.ClientRec