Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1

    Unanswered: Running total in SQL

    Good day,

    I have been trying to figure out how to get a running total using my sql statement and just can't.
    My current table has 2 fields:
    Value
    Name

    Then there are a whole bunch of entries:
    31 Jon
    23 George
    3 Bob
    4 Jon
    9 Jon
    98 Bob

    What I would like is from my outputed SQL is something like:
    23 Geogre 23
    43 Jon 66
    101 Bob 167

    Can anyone help me, this is really confusing for me and need it to work. I am not able to change the table layout in anyway what so ever and only have the 2 fields, Value Name to use.

    Please help me

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    what do you plan to order by?

    And what course are you taking?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    I would like to order by the first column.

    Ummmm....what course??? I am busy doing a report, for WORK. I have about 4 subreports, all working perfectly but the last part of the report is a graph in SSRS where I need to have both values plotted.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by plsh
    I would like to order by the first column.
    In that case shouldn't your desired output be more like:
    3 Bob 3
    4 Jon 7
    9 Jon 16
    ...

    Also - is Value unique?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Value is just that a value. I need it grouped by the name as well, you haven't done that....

    Value is not unique, can be multiple people with the same value. But the final output must must contain only one name with a total for the value for that person and then the running total sorted by value.

  6. #6
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    select val=31,name='Jon' into #t1 union all
    select 23, 'George' union all
    select 3, 'Bob' union all
    select 4, 'Jon' union all
    select 9, 'Jon' union all
    select 98, 'Bob' union all 
    select 90, 'Sam' union all 
    select 11, 'Sam' 
    
    select id=identity(int,1,1),sumval=sum(val),name 
    into #t2 from #t1 
    group by name
    order by 2,3
    
    select a.sumval,a.name, runsum=sum(b.sumval)
    from #t2 a,#t2 b
    where a.id>=b.id
    group by a.sumval,a.name
    order by a.sumval
    
    drop table #t1
    drop table #t2

  7. #7
    Join Date
    Jul 2006
    Posts
    87
    The other method above works very well. I had gone a different path for a solution. This version differs in that it uses only one temporary table, and only one embedded select query. The choice is yours.

    Code:
    CREATE TABLE #mytable 
    ( Items int, Names varchar(10) )
    
    INSERT #mytable VALUES (31, 'Jon')
    INSERT #mytable VALUES (23, 'George')
    INSERT #mytable VALUES (3, 'Bob')
    INSERT #mytable VALUES (4, 'Jon')
    INSERT #mytable VALUES (9, 'Jon')
    INSERT #mytable VALUES (98, 'Bob')
    
    SELECT TotalItems, Names, SUM(GroupTotalItems) AS CumulativeTotalItems
    FROM
    (SELECT   A.TotalItems, A.Names, B.GroupTotalItems
    FROM       
    (SELECT   SUM(Items) AS TotalItems, Names 
              FROM     #mytable
              GROUP BY Names) A
    INNER JOIN
    (SELECT   SUM(Items) AS GroupTotalItems 
              FROM     #mytable
              GROUP BY Names) B
    ON A.TotalItems >= B.GroupTotalItems) C
    GROUP BY TotalItems, Names
    ORDER BY TotalItems, Names
    
    DROP TABLE #mytable
    BTW, try to understand that your request had a homework feel to it, so that is why some might not consider it a query that should be worked on.
    Last edited by Code Carpenter; 07-25-06 at 10:21.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi CC

    One problem - what about pdreyer's Sam? Add the below and your running total loses it a bit.

    INSERT #mytable VALUES (101, 'Sam')
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jul 2006
    Posts
    87
    Quote Originally Posted by pootle flump
    Hi CC

    One problem - what about pdreyer's Sam? Add the below and your running total loses it a bit.

    INSERT #mytable VALUES (101, 'Sam')
    Nice catch. I guess that depends on how plsh wants to handle tiebreaker conditions.

    I mean, Sam has 269 in both solutions, but should Bob have 168 or also have 269?

    Or should Sam get moved up and have 168? We only have the order by of the totals to decide.

    But, if plsh says that Bob should have 168, then yeah, I would have to go to two tables, since I cannot insert an Order By on the internal list.

    BTW, the flump that you cuddle, is it a stuffed animal (like Calvin in Calvin and Hobbes) or is it living flump like a puppy?

    Just curious...

    CC.

  10. #10
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    Ok to clear up any confusion. I have a table, table1 with 2 fields, Value, Name, I will not know the contents of this table until I do a query.
    For example it now has the following in it:
    31 Jon
    23 George
    3 Bob
    4 Jon
    9 Jon
    98 Bob

    Using the following SQL statement:
    Select sum(Value), Name from table1 group by Name order by Sum(Value)

    From that I get:
    23 George
    44 Jon
    101 Bob

    Now what I want to get ultimately is an output of the following:
    23 George 23
    44 Jon 67
    101 Bob 168

    The problem is I cannot get it to work that the final column is a running total based on the output sort sequence I need.

    I am also not able to, or want to, use temp tables.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Code Carpenter
    BTW, the flump that you cuddle, is it a stuffed animal (like Calvin in Calvin and Hobbes) or is it living flump like a puppy?
    I'm a cuddling flump rather than a flump cuddler. Sorry for any confusion

    plsh - Have you run CCs code? What do you want to happen in the result of a SUM(Value) tie?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    What do you mean ccs code?

    How much more descriptive do I need to be, is my outputed result not self explanatory, please explain your question further.

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code Carpenters code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Nov 2004
    Posts
    285
    Provided Answers: 1
    What is that?

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Lol.

    Code Carpenter suggests a solution:
    Post #7
    I think it might not be complete.:
    Post #8

    If there is a tie for SUM(Value) - what do want to happen? If you want it to be handled the way Code Capenter's code handles it then you have your solution. Otherwise it needs refining.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •