how can i add two rows in

Question

0.00/5 (No votes)

See more:

I want this

name  id salary 
raj   1  90   90
ram   2  23  113
jeet  3  15  128
neelu 4  20  148

In ram 2 to get the balance I SUM Balance(90) of raj 1 with Total(23) of ram 2 to get Balance (113) of ram 2. Same thing is done for the next row by adding with Balance of previous row

Posted 28-May-13 7:30am

jitendra kachhi

Updated 28-May-13 8:25am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

v2

Add a Solution

Comments

Prasad Khandekar 28-May-13 14:40pm

Please try with following SQL.

WITH SAL_CTE AS (
SELECT name, id, salary,
ROW_NUMBER() OVER (ORDER BY id) rn
FROM @salary_table
)
SELECT a.name, a.id, SUM(ISNULL(b.salary, 0)) FROM SAL_CTE a
LEFT JOIN SAL_CTE b ON a.rn >= b.rn
GROUP BY a.id

You can find more help at following link(s)

1. http://msdn.microsoft.com/en-us/library/ms190766(v=sql.105).aspx

Regards,

RedDk 29-May-13 14:27pm

Msg 8120, Level 16, State 1, Line 6
Column 'SAL_CTE.name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

4 solutions

Solution 1

Looks like you want running totals, and there seems to be several solutions mentioned here -
[^]

Posted 28-May-13 8:34am

scottgp

Solution 3

Hi,

You can try like this also, If ID column is IDENTITY.

SQL

SELECT e.name, e.id, e.salary, (SELECT SUM(Salary) FROM emp WHERE id<=e.RNo) 'TotSalary'
FROM emp e

Regards,
GVPrabu

Posted 30-May-13 0:39am

gvprabu

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

damodara naidu betha · Accepted Answer · 2013-06-26T21:02:00

Solution 4

Hi try this code block,

SQL

CREATE TABLE YourTable (ID int,name varchar(20),salary money)

INSERT INTO YourTable (ID,name,salary) 
VALUES 
(1, 'raj',90),
(2,'ram', 23),
(3,'jeet',15 ),
(4,'neelu',20)

 ;WITH RecursiveCTE AS 
(
  SELECT TOP 1 ID, name,salary, salary as Total FROM YourTable ORDER BY ID asc
  UNION ALL
  SELECT YourTable.ID,YourTable.name,YourTable.salary,Total+YourTable.salary FROM 
  RecursiveCTE JOIN YourTable ON YourTable.ID = RecursiveCTE.ID + 1
)
 
 SELECT * FROM RecursiveCTE OPTION (Maxrecursion 0)

Happy coding...

Posted 26-Jun-13 21:02pm

damodara naidu betha

Updated 27-Jun-13 0:32am

v2

Comments

Raja Sekhar S 27-Jun-13 6:19am

This Will Work.... But if u have Id's more than 100 u have to use OPTION (Maxrecursion 1000)
..if u give 1000 it will loop in for maximum of 1000 times...
+5! For ur answer Damodara Naidu...

damodara naidu betha 27-Jun-13 6:25am

You are right.. Raja Sekhar. By default Maxrecursion limit is 100. If we are not sure about the count, then we can set OPTION (Maxrecursion 0) to remove limit. Thank you very much for your voting.

jitendra kachhi 27-Jun-13 13:44pm

thank u so much Raja Sekher i have another qus please
name id salary
its my question
raj 1 90
ram 2 23
neelu 3 15
neelu 4 20

there is two time neelu then out put should like this

raj 1 90 90
ram 2 23 113
neelu 3.1 15 128
neelu 3.2 20 148

jitendra kachhi · Accepted Answer · 2013-05-29T08:55:00

Solution 2

SQL

select a.Id, a.Name, sum(b.salary) as salary
from tablename  a cross join tablename b
where b.Id <= a.Id
group by a.Id, a.Name

Posted 29-May-13 8:55am

jitendra kachhi

Updated 30-May-13 0:31am

v2

Comments

gvprabu 30-May-13 6:32am

hi.... salary column is not there.

RedDk 30-May-13 13:14pm

... ORDER BY [id] ASC