SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases


Friday, May 26, 2006

Previous row calculations using SQL

SQL Apprentice Question
I need help in writing a SQL, where in a calculated column depends on
previous's row column, for example,


Table: test
Time Pkts Seq_no
---------------------------------
10:00 20 25
10:01 15 40
10:02 17 57
10:03 10 60
10:04 12 72


Query Result: The output of the query should be
Time Pkts Seq_no drops
-----------------------------------------
10:00 20 25 NULL
10:01 15 40 0
10:02 17 57 0
10:03 10 60 7 (=57+10-60)
10:04 12 72 0


Is this kind of calculation possible using SQL. Any help in this
regards will be highly appreciated.

Celko Answers
Get a copy of SQL-2005 and look at SUM() OVER() function that is new to
Standard SQL. It can get complicated but it is used for running
totals, etc.

No comments: