Good Query Writing......

SQL Query,
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
IS it possible to run a query where I can the Col2 as sum from previous
column for each row in col1?
Col1
Col2
1
1
2
3
2
5
3
8
4
12
Thanks,
Hilton
depends what you mean by previous column.
1, 2, 2,
Which 2 comes first? To define that you will need some other value to sort on
If neither is before the other then
select i, sum((select t2.i from tbl where t2.i <= t.i)) from tbl
If there's no other column to sort then use a temp table with an identity
select i, id = identity(int,1,1) as id into #a from tbl
select i, sum((select t2.i from #a where t2.i < t.i or (t2.i = t.i and t2.id
<= t.id)))
from #a
"John Hilton" wrote:

> SQL Query,
>
> Col1 in Table1 has the following data.
>
> Col1
> 1
> 2
> 2
> 3
> 4
>
>
> IS it possible to run a query where I can the Col2 as sum from previous
> column for each row in col1?
>
> Col1
> Col2
> 1
> 1
> 2
> 3
> 2
> 5
> 3
> 8
> 4
> 12
>
>
> Thanks,
> Hilton
>
>
|||Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton
|||Didn't I answer that?
Nigel Rivett
www.nigelrivett.net
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Here's an alternative to Nigel's:
SELECT [ID] = IDENTITY(INT, 1, 1)
,Col1
INTO #Temp
FROM tbl
ORDER BY Col1 ASC
SELECT Col1 = AVG(t1.Col1)
Col2 = SUM(t2.Col1)
FROM #Temp AS t1
INNER JOIN
#Temp AS t2
ON t1.[ID] >= t2.[ID]
GROUP BY t1.[ID]
ORDER BY t1.[ID]
DROP #Temp
Sincerely,
Anthony Thomas

"John Hilton" <John_Hilton2004@.hotmail.com> wrote in message
news:%23Fw2HOx4EHA.1188@.tk2msftngp13.phx.gbl...
Nigel Rivett, I really appricate your time in replying this post!!!
Since the formatting is lost in the post,
I am posting this again.
Col1 in Table1 has the following data.
Col1
1
2
2
3
4
Is it possible to run a query where I can get the Col2 as sum from previous
column for each row in col1?
So the query result should be as follows.
Col2
1
3
5
8
12
Thanks,
Hilton