Cumulative condition query
ID Total1 Total2 Total3 1 10 0 20 1 40 0 50 1 15 0 5
ID Total 1 140
How do i add cumulatively all the rows and find the matching total in table2?
How do i add cumulatively all the rows and find the NOT matching total in table2 and write update statement to update (add those) and write to table2 ?
select sum(t1.total1) + sum(t1.total2) +sum(t1.total3) as "Total" from table t1, table t2 where t1.ID=t2.ID and sum(t1.total1) + sum(t1.total2) +sum(t1.total3)=t2.total;
I am not able to get it correctly the update syntax.
Looks like you have the right query to find the totals and match them up. To update rows that don't have the same total, you could use a correlated subquery like this:
UPDATE table2 SET total = ( SELECT SUM(Total1 + Total2 + Total3) FROM table1 WHERE id = table2.id GROUP BY id )
Or with a join (slightly more complicated, but likely faster)
UPDATE t2 SET t2.total = t1.total FROM table2 t2 INNER JOIN ( SELECT id, SUM(Total1 + Total2 + Total3) as total FROM table1 GROUP BY id ) t1 ON t1.id = t2.id WHERE t2.total <> t1.total
select T1.* from ( select t1.ID, sum(t1.total1) + sum(t1.total2) + sum(t1.total3) as Total from table t1 group by t1.ID ) t1 inner join table t2 on t1.ID = t2.ID and t1.Total = t2.total