StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL Server calculating sum dynamically
text
Body
copied!<p>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:</p> <pre><code>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 </code></pre> <p>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).</p> <p>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. </p> <p>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.</p> <p>I hope I explained this adequately, I know there is a way to do this, I'm just not sure how to apply it.</p> <p>Here are examples of the two physical tables, and what the output aggregate looks like, and what it should look like:</p> <p>table d: <img src="https://i.stack.imgur.com/nBuyA.png" alt="first table in union"></p> <p>table a:</p> <p><img src="https://i.stack.imgur.com/wR03z.png" alt="second table in union"></p> <p>output table: THE REDISH rows are from table d, while the BLUE are from table a.</p> <p><img src="https://i.stack.imgur.com/iwfMH.png" alt="end output (aggregate)"></p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload