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.
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'
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
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
(SELECT A.TotalItems, A.Names, B.GroupTotalItems
(SELECT SUM(Items) AS TotalItems, Names
GROUP BY Names) A
(SELECT SUM(Items) AS GroupTotalItems
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.
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:
Using the following SQL statement:
Select sum(Value), Name from table1 group by Name order by Sum(Value)
From that I get:
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.