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 mewhat do you plan to order by?

And what course are you taking?|||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.|||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?|||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.|||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|||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.

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. :)|||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')|||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... :D

CC.|||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.|||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 :D

plsh - Have you run CCs code? What do you want to happen in the result of a SUM(Value) tie?|||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.|||Code Carpenters code.|||What is that?|||Lol.

Code Carpenter suggests a solution:
Post #7 (http://www.dbforums.com/showpost.php?p=6221328&postcount=7)
I think it might not be complete.:
Post #8 (http://www.dbforums.com/showpost.php?p=6221334&postcount=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|||BTW - Code Carpenters says "This version differs in that it uses only one temporary table". This isn't strictly true - the solution itself does not use a temp table.|||plsh, Code Carpenter. (hello)
Code Carpenter, plsh. (nice to meet you)

Okay, I have got to stop thinking about SQL when off duty. I sat there wondering why I couldn't get the list to be the format of pdreyer's excellent solution, when it dawned on me (ping!) that the NAMES ARE UNIQUE. So, I can add the second test of Name >= Name, to get rid of the tie value problem.

-- NOTE: This is only a Temp Table because we do not have yours.
-- Replace this table with your own table, names, and data types.
CREATE TABLE #YOURtable
( Items int, Names varchar(10) )

INSERT #YOURtable VALUES (31, 'Jon')
INSERT #YOURtable VALUES (23, 'George')
INSERT #YOURtable VALUES (3, 'Bob')
INSERT #YOURtable VALUES (4, 'Jon')
INSERT #YOURtable VALUES (9, 'Jon')
INSERT #YOURtable VALUES (98, 'Bob')
INSERT #YOURtable VALUES (10, 'Sam')
INSERT #YOURtable VALUES (91, 'Sam')

SELECT TotalItems, Names, SUM(GroupTotalItems) AS CumulativeTotalItems
FROM
(SELECT A.TotalItems, A.Names, B.GroupTotalItems
FROM
(SELECT SUM(Items) AS TotalItems, Names
FROM #YOURtable
GROUP BY Names) A
INNER JOIN
(SELECT SUM(Items) AS GroupTotalItems, Names
FROM #YOURtable
GROUP BY Names) B
ON A.TotalItems > B.GroupTotalItems
OR (A.TotalItems = B.GroupTotalItems AND
A.Names >= B.Names)) C
GROUP BY TotalItems, Names
ORDER BY CumulativeTotalItems, Names

-- NOTE: This is only a Temp Table because we do not have yours.
-- You can skip this Drop since you would not want to Drop your existing Table.
DROP TABLE #YOURtable

So, with this solution, there are no temp tables (yay), each cumulative total is unique (yipee), and (assuming that the names on a tie would be sorted by some method) the results go in proper order on the CumulativeTotalItems column (hooray!).

The crowd goes wild! (thank you, *bow*, thank you, *blows kiss*, and thank you most of all scarecrow!)

And my day starts off in a silly yet somehow slightly productive mood. :)|||Nice one CC - problem no more :)

The punchline though is - SQL doesn't tend to handle these sorts of things efficiently and it is usually best to get the FE to do it for you. I forgot I had this bookmarked away too:
http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp|||Wow, that article looks like it was written for plsh!

"Conclusion
None of the solutions mentioned is the one I prefer. The solution I recommend is to return a "raw" data set to the client and let the client loop through rows and calculate the running total. The query returning "raw" data is the fastest and the least resource intensive. Implementation of running total calculation is fast and straight forward for programming languages used on the client side."

And how true is that! Whether I am using Crystal Reports, a custom application, or simple HTML, the running total would be so much easier as a calculated variable when it is being displayed on the front end, sequentially.

l8r.|||I finally managed to get it to work...

CREATE TABLE #mytable
( Items int, Names nvarchar(100) )

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 Items, Names,
(Select sum(Items)
From #mytable
Where Items >= a.Items)
From #mytable a
order by 1 desc

DROP TABLE #mytable

Sorry guys for messing you around, but thanks for jogging my brain and helping me get everything to work.

Code Carpenter, sorry for jumping down your throat, but to be honest I wish this had been homework, then I could have copied it from someone...ha ha

this wasn't the ideal solution but it works.

Thanks and sorry to all who helped out with this.

Thanks again|||hi plsh

I guess you would know better but doesn't that solution fail to meet two of your criteria? (although the sorted descending is pretty trivial to be fair :) ). Also - if the values of Value are not unique you have another problem.

Anyway - if you are happy so are we but let us know if that fails. I think CCs code is closer to what you want (based on your posts).|||As I said it isn't ideal but it does work perfectly and it is very simle. One of the things I have been critisised for in the past by co workers is my code or solutions are to complex and this to me seems pretty simple, as do my co workers. So if it works, which it does, and my co workers are happy I guess it will have to do.

CCs code does work, but to be honest my co workers wont and can't follow that so going to stick to the simpler version.

Thanks again to all for their help.

Cheerssql