Cumulative condition query

Table 1:

ID      Total1 Total2 Total3
1         10       0          20
1         40       0          50
1         15       0          5

Table 2:

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.

Answers


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

Need Your Help

Why does `Array(0,1,2) == Array(0,1,2)` not return the expected result?

arrays scala equals equality

As far as I understand, Scala's == defines the natural equality of two objects.

Adding a second Wordpress widget

wordpress wordpress-theming sidebar

I'm having difficulty with setting up a second sidebar for my Wordpress theme.