Adding Row Values - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Adding Row Values

Author

Topic

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-01-11 : 18:38:29

I have a table as follows:


Date         Col1      
01/01/2011     5
01/02/2011     10
01/03/2011     15
01/04/2011     20

I want to create a 3rd column in a select statement that would add the value of the previous row to the value of the current row. So the resulting select statement would produce this:


Date         Col1     DerivedCol      
01/01/2011     5          
01/02/2011     10         15     --(row2 + row1)
01/03/2011     15         25     --(row3 + row2)
01/04/2011     20         35     --(row4 + row3)

Is this possible??

Thanks in advance,
rypi

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-01-11 : 19:06:44

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. If you know how, follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Sample data is also a good idea, along with clear specifications. It is very hard to debug code when you do not let us see it. If you want to learn how to ask a question on a Newsgroup, look at: http:--www.catb.org-~esr-faqs-smart-questions.html

If you don't know anything about RDBMS, then get a copy of the simplest intro book I know --
http:--www.amazon.com-Manga-Guide-Databases-Mana-Takahashi-dp-1593271905

Let's write DDL and fix the dates.
CREATE TABLE Foobar
(foo_date DATE NOT NULL PRIMARY KEY,
something_score INTEGER NOT NULL);

INSERT INTO Foobar
VALUES ('2011-01-01', 5'),
('2011-01-02', 10'),
('2011-01-03', 15'),
('2011-01-04', 20);

>> I want to create a 3rd column in a SELECT statement that would add the value of the previous row to the value of the current row. <<

SELECT F1.foo_date, F1.something_score,
(SELECT SUM (soomething_score)
FROM Foobar AS F2
WHERE F2.foo_date <= F1.foo_date) AS running_somethign_score
FROM Foobar AS F1;

Performance will be bad for large tables; it is best to do this in a report writer in the front end.

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 00:38:45

But where is the candidate key in your table which defines row1,row2,row3 and so on?
Is it the date column?If it is then will the date values be increasing in steps of a single day difference?

PBUH

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-01-12 : 00:59:39

That is a running tally of all rows. I just need the current row and the previous row added.


Row    Date         Col1     DerivedCol      
 1   01/01/2011     5         
 2   01/02/2011     10     (row2 + row1) -- =15
 3   01/03/2011     15     (row3 + row2) -- =25
 4   01/04/2011     20     (row4 + row3) -- =35

Sachin.Nand

2937 Posts

Posted - 2011-01-12 : 01:09:28

[code]
CREATE TABLE #Foobar
(foo_date DATE NOT NULL PRIMARY KEY,
something_score INTEGER NOT NULL);

INSERT INTO #Foobar(foo_date,something_score)
VALUES ('2011-01-01', 5),
('2011-01-02', 10),
('2011-01-03', 15),
('2011-01-04', 20);

;with cte
as
(
select *,row_number()over(order by (select 1))row from #Foobar

)
select foo_date,
f1.something_score,
coalesce(f1.something_score+f.something_score,0)DerivedCol from cte f1
outer apply(select top 1 something_score from cte f2 where f1.Row=f2.row+1)f

drop table #Foobar
[/code]

PS :-Performance wont be good for a huge table

PBUH

rypi
Yak Posting Veteran

55 Posts

Posted - 2011-01-12 : 09:15:49

Thanks for the help!!

lappin
Posting Yak Master

182 Posts

Posted - 2011-01-12 : 10:38:14

3 solutions: http://www.sqlteam.com/article/calculating-running-totals

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources