Running Total (pre 2012) for more than 1 group

Question

Sorry to bring this topic up again, I've come across several helpful existing posts that are very similar but stackoverflow won't let me post a comment/question on an existing thread because I'm so new (less than 50 points).

Anyways, a lot of the running total posts are great for 1 group / running total only. As soon as I introduce a second group (which I want the running total to start at 0 again) it keeps adding them up.

I've had success using the quirky update, CTE with recursion, etc... but not for multiple groups.

The basic ouput I'm looking for looks like this:

NAME    DATE        DOLLARS     RUNNING
John    1/1/2014    5           5
John    1/2/2014    3           8
John    1/2/2014    4           12
John    1/2/2014    8           20
John    1/3/2014    12          32
Matt    1/1/2014    2           2
Matt    1/2/2014    7           9
Matt    1/3/2014    10          19

How could I achieve this? Thanks a bunch in advance

Here are some of the articles I've come across that are helpful:

Partitioning results in a running totals query

Using "Update to a local variable" to calculate Grouped Running Totals

I have edited your title. Please see, ["Should questions include “tags” in their titles?"](http://meta.stackexchange.com/questions/19190/), where the consensus is "no, they should not". — Andrew Savinykh, Jan 09 '14 at 20:08
In your new question, how do you know which one of all the rows that are in the same date comes first? — Lamak, Jan 09 '14 at 21:03
It's really a datetime, so I already have them ordered by datetime - thanks — Jay, Jan 09 '14 at 21:18

Gordon Linoff · Accepted Answer · 2014-01-09T21:05:41.390

2

You can do this using a correlated subquery:

select name, date, dollars,
       (select sum(dollars)
        from table t2
        where t2.name = t.name and
              t2.date <= t.date
       ) as running
from table t;

EDIT:

If you have multiple rows on the same date, then you need to introduce another ordering criterion. The data in the question has no other order column (such as an id). So, we can create one:

with t as (
      select t.*, row_number() over (partition by name order by date) as seqnum
      from table t
     )
select name, date, dollars,
       (select sum(dollars)
        from t t2
        where t2.name = t.name and
              t2.seqnum <= t.seqnum
       ) as running
from t t;

The only problem is that the rows within a date will be an in arbitrary order, that could even change between executions of the query. SQL tables are inherently unordered, so you need a column to specify the ordering.

edited Jan 09 '14 at 21:05

answered Jan 09 '14 at 20:04

Gordon Linoff

1,242,037
58
646
786

Thanks THIS WORKS, however if there are multiple rows for the same date, it returns me the _total_ of the _running total_ for each row in that date group. I've edited my post above (sorry). Can a slight modification to your query yield those results? Also, if I were to introduce another column for grouping (like COMPANY for example) - could I handle that through this? Thanks so much!! – Jay Jan 09 '14 at 21:00
Thanks again for the response! For some reason this is still giving me the total for the day in every row for each date, for example, JOHN on 1/2/2014 is getting '20' in the RUNNING column for the 3 rows. – Jay Jan 10 '14 at 15:48
That is very odd behavior. The `t` subquery should have a sequence number that is distinct on each row. If you have an `id` column, then add it to the `order by` to make the sort stable (`order by date, id`). – Gordon Linoff Jan 10 '14 at 21:03

score 2 · Answer 2 · answered Jan 09 '14 at 20:08

You can use OUTER APPLY:

SELECT  A.[NAME],
        A.[DATE],
        A.DOLLARS,
        SUM(B.[DOLLARS]) RUNNING
FROM YourTable A
OUTER APPLY (SELECT *
             FROM YourTable
             WHERE [NAME] = A.[NAME]
             AND [DATE] <= A.[DATE]) B
GROUP BY A.[NAME],
         A.[DATE],
         A.DOLLARS
ORDER BY A.[NAME],
         A.[DATE],
         A.DOLLARS

Here is a demo for you to try. The results are:

╔══════╦════════════╦═════════╦═════════╗
║ NAME ║    DATE    ║ DOLLARS ║ RUNNING ║
╠══════╬════════════╬═════════╬═════════╣
║ John ║ 2014-01-01 ║       5 ║       5 ║
║ John ║ 2014-01-02 ║       3 ║       8 ║
║ John ║ 2014-01-03 ║      12 ║      20 ║
║ Matt ║ 2014-01-01 ║       2 ║       2 ║
║ Matt ║ 2014-01-02 ║       7 ║       9 ║
║ Matt ║ 2014-01-03 ║      10 ║      19 ║
╚══════╩════════════╩═════════╩═════════╝

Thanks a bunch - this gave me the results I had asked for but the query above yielded the results a little faster! Thanks again — Jay, Jan 09 '14 at 21:02

score 0 · Answer 3 · answered Jan 09 '14 at 21:04

0

You may to use ROLLUP or CUBE like this:

select name, date, dollars, sum(dollars) running
from table t
group by name, date, dollars with rollup

answered Jan 09 '14 at 21:04

Andrey Khmelev

1,141
8
13

Running Total (pre 2012) for more than 1 group

3 Answers3

Linked