Microsoft SQL Server forum - sum with increment

Author

sum with increment

cahyadi

2006-03-08, 3:23 am

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

David Portas

2006-03-08, 7:24 am

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

Doug

2006-03-08, 8:24 pm

What class are you taking that this is homework for????

cahyadi

2006-03-08, 8:24 pm

thanks for your information, but if i want increment 7 i try syntax :
SELECT A.autobaris, B.autobaris AS Expr1, A.nilai + B.nilai AS tot
FROM ac A LEFT OUTER JOIN
ac B ON A.autobaris + 4 = B.autobaris
and the result :
autobaris exp1 tot
1 4 9
5 8 22
9 null null
but i want the result :
autobaris exp1 tot
1 4 20
5 8 44
9 10 18
please help thanks

2006-03-09, 3:23 am

Ummm, why A.autobaris + 4 if you want an increment of 7? shouldnt you
specify A.autobaris + 6?

MC

"cahyadi" <csastra99@gmail.com> wrote in message
news:1141867886.185553.79400@p10g2000cwp.googlegroups.com...
> thanks for your information, but if i want increment 7 i try syntax :
> SELECT A.autobaris, B.autobaris AS Expr1, A.nilai + B.nilai AS tot
> FROM ac A LEFT OUTER JOIN
> ac B ON A.autobaris + 4 = B.autobaris
> and the result :
> autobaris exp1 tot
> 1 4 9
> 5 8 22
> 9 null null
> but i want the result :
> autobaris exp1 tot
> 1 4 20
> 5 8 44
> 9 10 18
> please help thanks
>

cahyadi

2006-03-09, 7:25 am

that is sample......
in my aplikasi autobaris is date of transaction. and in my aplication
increment is dinamis. thanks.
sory for my english..

Sponsored Links