Post reply

cumulative aggregate funtion

  • September 8, 2008 at 2:43 pm

    #197408

    hi guys,

    i have a query with cumulative total.

    for the Column in a table, i need a Cumulative Total.Let me explain it to you .

    For example in the column shp_units in a table aggregatedata there are different values

    this is a example not a real value

    1 0

    2 2

    3 3

    4 3

    5 4554 .....some thing like that

    total for this column is 4562

    Now i want to display the cumulative like

    For the first row it will be 0 /total(Shp_units)

    And for Second Row it will be previos row + current Row /(total(shpunits) which is 0+2/4562

    and next shud be 0+2+3/4562

    0+2+3+3/4562

    and next

    4554+0+3+3+2

    so if you can help me,that will be great.

    thanks

  • September 9, 2008 at 5:46 am

    #868962

    Here is a CROSS APPLY solution:

    [font="Courier New"]CREATE TABLE #tmpData

    (Row INT NOT NULL

    , Val INT NOT NULL)

    INSERT #tmpData VALUES (1,12)

    INSERT #tmpData VALUES (2,6)

    INSERT #tmpData VALUES (4,8)

    INSERT #tmpData VALUES (5,15)

    INSERT #tmpData VALUES (6,7)

    INSERT #tmpData VALUES (9,2)

    SELECT

    *

    FROM

    #tmpData T

    CROSS APPLY (SELECT SUM(X.Val) AS TotalVal FROM #tmpData X

    WHERE X.Row <= T.Row) Y[/font]

    This will generate a hidden cursor, so for a lot of records, the performance will end up suffering. If you are lucky, Jeff may post a tally table solution that will be faster, or you could search the site a bit for Jeff Moden's postings.

  • September 9, 2008 at 11:30 am

    #869192

    thanks a lot

  • September 9, 2008 at 12:14 pm

    #869227

    Jeff's article on running totals is at: http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • September 9, 2008 at 12:23 pm

    #869235

    thanks a lot Gsquared..i appreciate it

  • September 9, 2008 at 12:57 pm

    #869253

    You're welcome.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Viewing 6 posts - 1 through 5 (of 5 total)

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