Replacement Of Sum(Column)Over(Partition By )

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Replacement Of Sum(Column)Over(Partition By )

Author

Topic

ijaziqbal
Starting Member

3 Posts

Posted - 2008-11-17 : 05:34:31

Hi All
Can anyone tell me what is the replacement of
sum(columnName) over(Partition By ColunName Order By columnName) as Alias, in sql server 2000. This function is supported in sqlserver2005 and 2008 and in oracle as well but I tried this in sqlserver2000 but it was not supported. So I want to know the replacement.
Regards

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-17 : 05:44:19

No direct replacement. you have to create a derived table with query below and join to that

SELECT ColunName,SUM(ColumnName) AS Sum
FROM YourTable
GROUP BY ColunName

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-17 : 06:26:26

1) Store the intermediate result in a temp table
2) Write a correlated subquery on the temp table

E 12°55'05.63"
N 56°04'39.26"

ijaziqbal
Starting Member

3 Posts

Posted - 2008-11-18 : 02:07:10

Actually I wanted to calculate running total.
e.g I have a table Stock
TrnId - ProductId - Quantity
1 - 1 - 10
2 - 1 - -2
3 - 2 - 10
4 - 1 - -4
5 - 2 - -6
6 - 1 - 5
7 - 2 - -3
Let say above is the table structure and I want my record like below

TrnId - ProductId - Quantity - Balance
1 - 1 - 10 - 10
2 - 1 - -2 - 8
3 - 2 - 10 - 10
4 - 1 - -4 - 4
5 - 2 - -6 - 4
6 - 1 - 5 - 9
7 - 2 - -3 - 1
One way to achieve this goal is the query below
select TrnId,ProductId,Quantity,(select sum(Quantity) from stock t
where t.ProductId=s.productId
and t.TrnId<=s.TrnId) as Balance from stock s

but this will slow down the server if I will try to calculate hundreds of thousands of records.
In Sql server 2005 and 2008 and in oracle
this could be done very efficiently by using
select TrnId,ProductId,Quantity,sum(Quantity)over(partition by ProductId order by TrnId) as Balance
but I am looking for an efficient solution in sql server 2000

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-18 : 22:30:03

select s.TrnId,s.ProductId,s.Quantity,sum(m.Quantity) from stock s
inner join stock m
on m.ProductId=s.productId
and m.TrnId<=s.TrnId
group by s.TrnId,s.ProductId,s.Quantity

Make Covering index and see execution plan.

ijaziqbal
Starting Member

3 Posts

Posted - 2008-11-21 : 04:27:25

Hi Sodeep,
Thanks for your solution.I exactly asked about this solution.
This query helped me a lot. I want to ask one more question.
What is the replacement of row_number() function in sql server 2000.
Thanks again for the solution

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-21 : 04:32:17

Visakh already gave you an answer for that.
There is no "replacement" for ROW_NUMBER() function in SQL Server 2000.
You will have to use a technique shown by sodeep.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources