SQL Server calculating sum dynamically

Question

I'm writing a query that creates an aggregate of a union of two tables, but I need the second table of the union to have some of the columns (sum)ed, and i'm not sure exactly the way to do this. This isn't part of a stored proc, so no variables. The details are below:

select t.col1, t.col2, t.col3 FROM (
    select d.col1, d.col2, d.col3 FROM table1 d
    join another_table
      on ...
    join different table
      on ...

    UNION ALL

    select a.col1, a.col2, a.col3 FROM table2 a
        join another_table
          on ...
        join different table
          on ...
    WHERE
       /*datetime restricitons */

) t

So this is the problem: col3 in tables a and d are the same data, except in table b it is a total value, with each new row that is added being calculated based on the previous. (I have no control over these tables).

After the union, the 30 or so extra rows from table a gets added to the end of the rows from table d, the only problem is that table a isnt a cumulative value, its per row by itself.

Ideally, during the union, I would want to take the last row in table d's col3 value, and then the first row added from table a would be the val from table d's col3 +/- the value in col3 for table a.

I hope I explained this adequately, I know there is a way to do this, I'm just not sure how to apply it.

Here are examples of the two physical tables, and what the output aggregate looks like, and what it should look like:

table d: first table in union

table a:

second table in union

output table: THE REDISH rows are from table d, while the BLUE are from table a.

end output (aggregate)

Not sure I completely get you, but I'd be tempted by quick and dirty on this one. Declare a local variable select the right value into it and then use it in where you need it. — Tony Hopkinson, Aug 14 '12 at 20:47
Hint: Corelated subquery ...see the last answer in this thread Not complete answer but a hint http://stackoverflow.com/questions/1153879/how-do-i-calculate-a-running-total-in-sql-without-using-a-cursor — ClearLogic, Aug 14 '12 at 20:47
Can you provide some example data and what the output should be? — Gordon Linoff, Aug 14 '12 at 21:12
good idea, that will make it much more clear. I'll edit original post now. — jz3, Aug 14 '12 at 21:19
I think you're going to need to update your example data, as it doesn't include rows 5 and 6 from your results. — Clockwork-Muse, Aug 14 '12 at 22:47

score 0 · Answer 1 · answered Aug 15 '12 at 00:01

OK, I'm a bit curious what sort of output you actually want because I cannot conceive of a use for the example result you've given even if the blue stuff is correct, so I'd like to suggest a way of getting the correct blue stuff and leave you to decide if you want to include the red stuff as well or not.

We need to identify the columns from table A except that col3 isnt col3 but is in fact a running total of col3 for whatever column identifies the type of data in table D, which I'll assume is column TYPE. Since this running total needs to incorporate prior data but not subsequent data, we'll also need a datetime or sequence column of some sort, so I'll call this column DATE

select alias_a.ID, alias_a.TYPE, alias_a.DATE, sum(b.VALUE) as Accum
from a alias_a join (
    select TYPE, col3 as VALUE from a where DATE <= alias_a.DATE
    ) b ON alias_a.TYPE = b.TYPE

now this data can be joined with table D to get the data:

select d.TYPE, ..., x.ID, x.DATE, (d.col3 + x.Accum) as RUNNING_TOTAL
from d join (
    select alias_a.ID, alias_a.TYPE, alias_a.DATE, sum(b.VALUE) as Accum
    from a alias_a join (
        select TYPE, col3 as VALUE from a where DATE <= alias_a.DATE
        ) b ON alias_a.TYPE = b.TYPE
) x on d.TYPE = x.TYPE 
order by d.TYPE, x.DATE

Does this help?

SQL Server calculating sum dynamically

1 Answers1