Re: sum with increment

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
--

.

Relevant Pages

  • Re: Multi-Field Primary Key
    ... I.E. the worst arrangement is to have the autonumber alone be the ... Such a key could implemented using a UNIQUE constraint i.e. ... you are a fan of the 'CASCADE to null' feature in Jet 4.0 ). ... SQL Server will disregard all values in the referencing columns if one ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Two Tables Insert Into with Select @@ Identity
    ... If you're using SQL Server ... INSERT commands - you can't enter values into an AutoNumber column w/o ... If you're using straight JET you can use VBA to open a Recordset to ... ' Run the 2nd INSERT INTO command. ...
    (microsoft.public.access.queries)
  • Re: Two Tables Insert Into with Select @@ Identity
    ... If you're using SQL Server ... INSERT commands - you can't enter values into an AutoNumber column w/o ... ' Run the 2nd INSERT INTO command. ... if field2 was a string: ...
    (microsoft.public.access.queries)
  • Re: Two Tables Insert Into with Select @@ Identity
    ... Charset: noconv ... If you're using SQL Server ... INSERT commands - you can't enter values into an AutoNumber column w/o ... ' Run the 2nd INSERT INTO command. ...
    (microsoft.public.access.queries)
  • Re: Renumbering MS db ID?
    ... I'm not an Access user, but as far as the notion that 'there is no need to ... MS Access or MS SQL Server? ... There is no need to care what the ... > guarrantees that and it is the only purpose autonumber is used for. ...
    (microsoft.public.dotnet.framework.adonet)
Loading