Posted by David Portas on 03/08/06 12:33

cahyadi wrote:
> TO all
> I have tabel
> autonumber value
> 1 1
> 2 5
> 3 6
> 4 8
> 5 9
> 6 10
> 7 12
> 8 13
> 9 8
> 10 10
> and if i want sum with increment 2
> autonumber value
> 1 - 2 1 + 5 = 6
> 3 - 4 6 + 8 = 14
> 5 - 6 19
> 7 - 8 25
> 9 - 10 18
>
> please help.............. trims

If "autonumber" really means an IDENTITY column then this looks like a
very silly and impractical design. What exactly is this data supposed
to represent?

Apparently you want to sum pairs of rows:

SELECT A.autonumber, B.autonumber
A.value+B.value AS tot
FROM tbl AS A
LEFT JOIN tbl AS B
ON A.autonumber + 1 = B.autonumber
WHERE A.autonumber IN (1,3,5,7,9) ;

(untested)

The problem is that you can't prevent gaps in the sequence of values in
an auto-numbered IDENTITY column. So there is no guarantee that each
row will have a "next" row.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Back to original message]