adding column vlaues with one another

Hi Guys,
I have a table like this
Groupid Salary
B01 1000
B01 2000
B01 3000
B02 2000
B02 2000
B02 1000
I wish to show like this
Groupid Salary Sum1
B01 1000 1000
B01 2000 3000
B01 3000 5000
Sum
6000
B02 2000 2000
B02 2000 4000
B02 1000 5000
Sum
5000
Plz Help me ASAPHi,
Create Table #TestTable
(
ID INT,
Value INT
)
INSERt INTO #TestTable
VALUEs (1,100)
INSERt INTO #TestTable
VALUEs (1,100)
INSERt INTO #TestTable
VALUEs (3,100)
INSERt INTO #TestTable
VALUEs (2,100)
INSERt INTO #TestTable
VALUEs (2,100)
INSERt INTO #TestTable
VALUEs (2,100)
Select * from #TestTable
order by [ID]
Compute SUM(Value) by [ID]
Drop TABLE #TestTable
HTH, jens Suessmeyer.|||Manish Sukhija wrote:

> I have a table like this
> Groupid Salary
> B01 1000
> B01 2000
> B01 3000
> B02 2000
> B02 2000
> B02 1000
> I wish to show like this
> Groupid Salary Sum1
> B01 1000 1000
> B01 2000 3000
> B01 3000 5000
> Sum
> 6000
> B02 2000 2000
> B02 2000 4000
> B02 1000 5000
> Sum
> 5000
> Plz Help me ASAP
This is something you need to do in your application, not really
something for the database. Most report generators support this.
HTH,
Stijn Verrept.|||Manish
Why not doing such reports on the client side?
CREATE TABLE #Test
(
PK INT NOT NULL PRIMARY KEY,
Groupid CHAR(3) NOT NULL,
Salary INT NOT NULL
)
INSERT INTO #Test VALUES (1,'B01',1000)
INSERT INTO #Test VALUES (2,'B01',2000)
INSERT INTO #Test VALUES (3,'B01',3000)
INSERT INTO #Test VALUES (4,'B02',2000)
INSERT INTO #Test VALUES (5,'B02',2000)
INSERT INTO #Test VALUES (6,'B02',1000)
SELECT Groupid,Salary,(SELECT SUM(Salary) FROM #Test T WHERE T.PK<=#Test.PK
AND T.Groupid='B01') Summary
FROM #Test WHERE Groupid='B01'
COMPUTE SUM(Salary)
SELECT Groupid,Salary,(SELECT SUM(Salary) FROM #Test T WHERE T.PK<=#Test.PK
AND T.Groupid='B02') Summary
FROM #Test WHERE Groupid='B02'
COMPUTE SUM(Salary)
"Manish Sukhija" <ManishSukhija@.discussions.microsoft.com> wrote in message
news:B0840D6C-F7E8-4C88-8822-167AC0A83C8B@.microsoft.com...
> Hi Guys,
> I have a table like this
> Groupid Salary
> B01 1000
> B01 2000
> B01 3000
> B02 2000
> B02 2000
> B02 1000
> I wish to show like this
> Groupid Salary Sum1
> B01 1000 1000
> B01 2000 3000
> B01 3000 5000
> Sum
> 6000
> B02 2000 2000
> B02 2000 4000
> B02 1000 5000
> Sum
> 5000
> Plz Help me ASAP
>|||Manish Sukhija wrote:
> Hi Guys,
> I have a table like this
> Groupid Salary
> B01 1000
> B01 2000
> B01 3000
> B02 2000
> B02 2000
> B02 1000
> I wish to show like this
> Groupid Salary Sum1
> B01 1000 1000
> B01 2000 3000
> B01 3000 5000
> Sum
> 6000
> B02 2000 2000
> B02 2000 4000
> B02 1000 5000
> Sum
> 5000
> Plz Help me ASAP
Judging by your required output, Sum1 is supposed to be a rolling
total. What determines the order in which the rows are to be added?
What are the key(s) your table? Your sketch of your table apparently
doesn't have a key at all! Please include DDL so that we don't have to
guess.
Let's assume you have a table that looks like this:
CREATE TABLE your_table (groupid CHAR(3) NOT NULL, dt DATETIME NOT
NULL, salary NUMERIC(10,2) NOT NULL, PRIMARY KEY (groupid, dt)) ;
Note the extra column and the key. Now try:
SELECT groupid, dt,
(SELECT SUM(salary)
FROM your_table
WHERE groupid = T.groupid
AND dt <= T.dt)
FROM your_table AS T
ORDER BY groupid, dt ;
You also have some extra totals in your output. You can try the
following, or take a look at the ROLLUP feature in Books Online.
SELECT groupid, dt,
(SELECT SUM(salary)
FROM your_table
WHERE groupid = T.groupid
AND dt <= T.dt)
FROM your_table AS T
UNION
SELECT groupid, '9999-12-31', SUM(salary)
FROM your_table
GROUP BY groupid
ORDER BY groupid, dt ;
David Portas
SQL Server MVP
--