Post reply

Sum of previous rows till this row.

  • February 17, 2009 at 11:17 pm

    #75417

    reate table temp (id int identity(1,1),Sum1 int)

    INSERT INTO TEMP(Sum1) select 10

    INSERT INTO TEMP(Sum1) select 20

    INSERT INTO TEMP(Sum1) select 30

    INSERT INTO TEMP(Sum1) select 40

    INSERT INTO TEMP(Sum1) select 50

    INSERT INTO TEMP(Sum1) select 60

    I should get the output as given below

    ID Sum1

    110

    230

    360

    4100

    5150

    6210

    Every row I should get the Sum of Sum1 from the prededing row

    How can I do this?

    DROP TABLE Temp

  • February 18, 2009 at 4:42 am

    #945786

    create table #tmp (fldID int identity(1, 1), fldNum int)

    insert into #tmp (fldNum)

    select 1 as x union all

    select 2 as x union all

    select 3 as x union all

    select 4 as x union all

    select 5 as x union all

    select 6 as x union all

    select 7 as x union all

    select 8 as x union all

    select 9 as x

    select A.fldID, A.fldNum, (select isnull(sum(B.fldNum), 0) from #tmp B where B.fldID < A.fldID) as mySum from #tmp A

    drop table #tmp

  • February 18, 2009 at 5:03 am

    #945800

    The output I get for this is

    id sum1 Mysum

    1100

    22010

    33030

    44060

    550100

    660150

    Which is not my requirement?

  • February 18, 2009 at 5:19 am

    #945803

    Try This:

    select b.sum1,sum(a.sum1) as cum_sum

    from temp a,temp b

    where a.id <= b.id

    group by b.id,b.sum1

    --where cum_sum will be your cumulative sum

  • February 18, 2009 at 5:39 am

    #945818

    Thanks this is what I wanted.

  • February 18, 2009 at 5:56 am

    #945825

    Ashwin M N, please forgive the flames but it had to have taken you longer to write that the answer didn't match your requirements than it would have taken for you to apply the answer to your requirements.

    What ever happened to trying?

  • March 4, 2009 at 12:21 am

    #952894

    Actually, all of the answers given use a performance crushing technique that will kill a server with relatively few rows. It's called a "Triangular Join" and it can be thousands and millions of times worse than a cursor with as little as 10,000 or 20,000 rows. Forget it if you have more than that. Read all about in the following article...

    http://www.sqlservercentral.com/articles/T-SQL/61539/

    ... and then tell me how many rows you have in your real table so we can put some peformance into the task.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 4, 2009 at 6:44 am

    #953106

    Is this a better approach, Jeff?

    set nocount on

    set statistics io on

    set statistics time on

    -- Drop if necessary

    drop table #tbl

    create table #tbl (IID int identity(1, 1), num int)

    create clustered index tmpIndexIID on #tbl(IID)

    insert into #tbl(num) values (0)

    while(select count(IID) from #tbl) < 10000

    insert into #tbl (num) select num from #tbl

    -- Assign the num value based on the ID

    update #tbl set num = IID

    -- How to get a sum of all records prior to each record

    select A.num, sum(B.num) as rowsum from

    #tbl A inner join #tbl B on A.IID >= B.IID

    group by A.num order by A.num

    -- Yields the following stats:

    -- Table '#tbl00000000822A'. Scan count 16386, logical reads 306674,

    -- physical reads 0, read-ahead reads 0.

    --SQL Server Execution Times:

    -- CPU time = 158249 ms, elapsed time = 79962 ms.

  • March 4, 2009 at 8:21 am

    #953242

    I knew I remembered a way to do this with variables!

    Credit where it's due, Jeff showed me this a while ago. It took me a while to remember it.

    set nocount on

    set statistics io on

    set statistics time on

    -- Drop if necessary

    drop table #tbl

    create table #tbl (IID int identity(1, 1), num int, rowsum bigint)

    create clustered index tmpIndexIID on #tbl(IID)

    insert into #tbl(num, rowsum) values (0, 0)

    while(select count(IID) from #tbl) < 500000

    insert into #tbl (num, rowsum) select num, rowsum from #tbl

    -- Assign the num value based on the ID

    update #tbl set num = IID

    declare @sum bigint

    set @sum = 0

    update #tbl set @sum = rowsum = (@sum + num)

    select * FROM #tbl order by IID

    -- The update stats:

    -- Table '#tbl00000000822A'. Scan count 1, logical reads 1626, physical

    -- reads 0, read-ahead reads 0.

    --SQL Server Execution Times:

    -- CPU time = 2125 ms, elapsed time = 2190 ms.

    -- on 524,000 + rows

  • March 4, 2009 at 4:07 pm

    #953676

    souLTower (3/4/2009)

    I knew I remembered a way to do this with variables!

    Heh... yep... that's the way, ST. Just gotta remember that you need to add a WITH (INDEX(0)) (didn't tell you about that before) to the fray to force the index scan should it not actually happen by itself...

    update #tbl set @sum = rowsum = (@sum + num)

    FROM #tbl WITH (INDEX(0))

    Since that will eventually lock the whole table, save the server a couple of clock cycles... do it up front....

    update #tbl set @sum = rowsum = (@sum + num)

    FROM #tbl WITH (INDEX(0),TABLOCKX)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 5, 2009 at 7:56 am

    #954047

    Jeff, quick question. Since I used a clustered index couldn't I "assume" that the order would be as I want it?

    I know to never assume...

    ST

  • March 5, 2009 at 1:35 pm

    #954355

    souLTower (3/5/2009)

    Jeff, quick question. Since I used a clustered index couldn't I "assume" that the order would be as I want it?

    I know to never assume...

    ST

    Correct... never assume unless you actually want bad data some day. 😀

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply