Adding each row with aprevious row in sql query

Question

Lets say I have a table with a values below:

    Date     sales
   =====     =====
    Jan      100
    Feb      150
    Mar      500

and so on

How can I query this table with the results below:

Date      Sales      Total
====      =====      ======
Jan        100        100
Feb        150        250 (Jan + Feb)
Mar        500        750 (Jan + Feb + mar)

I know it can be done in SP looping through but is there a simple query?

your help is appreciated.

Thanks, J

SQL tables represent unordered sets. There is no "previous" or "next" row, unless a column explicitly represents the ordering. If you are using the "Date" column for this purpose, you are limited to 12 rows. Perhaps you should rephrase your question, with data more similar to what you are really doing. Also, tag your question with the database you are using. — Gordon Linoff, Jun 30 '15 at 18:19
Most DBMSes support Windowed Aggregate Functions, you need a Cumulative Sum: `SUM(sales) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING)` — dnoeth, Jun 30 '15 at 18:19
possible duplicate http://stackoverflow.com/questions/2120544/how-to-get-cumulative-sum — kavetiraviteja, Jun 30 '15 at 19:20

Jason W · Accepted Answer · 2015-06-30T19:12:29.670

A windowed SUM should work on several DBMS. A SQL Server example is below (please let us know which one you are using otherwise):

DECLARE @T TABLE ([Date] DATE, [Sales] INT)
INSERT @T VALUES ('1/1/2015', 100), ('2/1/2015', 150), ('3/1/2015', 500)

SELECT
    [Date],
    [Sales],
    SUM([Sales]) OVER (ORDER BY [Date]) AS [Total]
FROM @T
ORDER BY
    [Date]

This generates the following output:

Date       Sales       Total
---------- ----------- -----------
2015-01-01 100         100
2015-02-01 150         250
2015-03-01 500         750

Since SQL Server 2008 doesn't support the ORDER BY in a windowed aggregate (only since 2012), here's a method to do same thing. It's very inefficient - there's just not a very efficient way to do this otherwise I've seen unfortunately.

;WITH CTE AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY [Date]) AS RowId,
        [Date],
        [Sales]
    FROM @T
)
    SELECT
        A.[Date],
        A.[Sales],
        SUM(B.[Sales]) AS [Total]
    FROM CTE A
        INNER JOIN CTE B
            ON B.RowId <= A.RowId
    GROUP BY
        A.[Date],
        A.[Sales]
    ORDER BY
        A.[Date]

Thank you! I am working with MSSQL 2008 R2 and the code above does not work for me :-( It does not like order. I tried PARTITION BY. It runs but does not add the numbers. — Jalal, Jun 30 '15 at 18:58
The ORDER BY is not supported until 2012. I updated the answer with a method to do it in 2008. As I mentioned, there's just not a good way to do it unfortunately, but it works. — Jason W, Jun 30 '15 at 19:13
Glad it did. Please feel free to upvote or accept the answer if you're happy with it. — Jason W, Jun 30 '15 at 20:46

Adding each row with aprevious row in sql query

1 Answers1