Add the last two columns values...

Anurag Singh

11y1.3k

Hi,

I have a table in which I have two columns Id and Value and I want the output like Sum(add last two columns).

Id Value Sum

1 10 10

2 20 30

3 30 50

4 40 70

5 50 90

Thanks & Regards:

Anurag Singh

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Pankaj Kumar Choudhary11y ago
Hello @Anurag you can try this method

SELECT mt.id,mt.value , ISNULL( mt.value+mt1.value,mt.value) AS count FROM My_Tab mt
LEFT OUTER JOIN
My_Tab mt1
ON
mt.id=mt1.id +1
Najjaf Shuja11y ago
SELECT ID, VALUE, (ID + VALUE) AS SUM FROM TABLENAME
Upendra Pratap Shahi11y ago
Hello Anurag,

Try my query demo.
Manoj Bhoir11y ago
Welcome Anurag.
Anurag Singh11y ago
Thanks Manoj,

Your query is working as expected.

Anurag Singh
Manoj Bhoir11y ago
Hi Anurag,

Please check my Reply. It will give you exact output you want.
Khan Abrar Ahmed11y ago
Hi try isnull with 0,

declare @tableName as table (
id INT ,
value int
)
INSERT INTO @tableName values (1,10),(2,20),(3,30),(4,40),(5,50);

WITH tblDifference AS
(
SELECT id,ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, value
FROM @tableName
)
SELECT cur.id,cur.value, cur.value + isnull(previous.value,0) as Sum
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1;
Upendra Pratap Shahi11y ago
Kindly write your query accordingly.
Rajeesh Menoth11y ago
Hi,

You can use "Case" statement in ur query.putt "0" or any other value for "Null" value..

Case in sql Example : http://www.aspdotnet-suresh.com/2012/07/query-to-use-case-statement-in-sql.html

Another example : http://stackoverflow.com/questions/17869227/sql-server-case-statement-when-is-null
Anurag Singh11y ago
Thanks,

But this is showing null in first column. I don't want null value anywhere. In first column, there should be the value of first column.

Anurag
Upendra Pratap Shahi11y ago
Run below query-

CREATE TABLE tableName (
id INT IDENTITY(1,1),
columnOfNumbers int
)
INSERT INTO tableName SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 10 UNION SELECT 12

WITH tblDifference AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers
FROM tableName
)
SELECT cur.columnOfNumbers, cur.columnOfNumbers + previous.columnOfNumbers
FROM tblDifference cur
LEFT OUTER JOIN tblDifference previous
ON cur.RowNumber = previous.RowNumber + 1
Manoj Bhoir11y ago
Hi Anurag,

Try this query :

WITH GetSum AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, value
FROM TableName
)

SELECT cur.value, ISNULL(cur.value + previous.value,cur.value)
FROM GetSum cur
LEFT OUTER JOIN GetSum previous
ON cur.RowNumber = previous.RowNumber + 1

Replace TableName with your Table Name.
Rajeesh Menoth11y ago
Hi,

Check this demo for your question : http://sqlfiddle.com/#!3/ad6e5/11

or

WITH SumDetails AS
(
SELECT ROW_NUMBER() OVER(ORDER BY id) AS RowNumber, columnOfNumbers
FROM tableName
)
SELECT cur.columnOfNumbers, cur.columnOfNumbers + previous.columnOfNumbers
FROM SumDetails cur
LEFT OUTER JOIN SumDetails previous
ON cur.RowNumber = previous.RowNumber + 1