TaibaDXB
asked on
SQL Server Sum Query
Hi.
I have a table similar to this:
Orders
======
OrderID TotalWeight
====== ==========
1 100
2 150
3 400
.
.
.
I need to get this output:
OrderID TotalWeight TempTotalWeight
====== ========== ==============
1 100 100 ( 0 + 100 )
2 150 250 ( 100 + 150 )
3 400 650 ( 100 + 150 + 400 )
How to write the sql statement for it with the best performance ( I have a lot of Data )?
I have a table similar to this:
Orders
======
OrderID TotalWeight
====== ==========
1 100
2 150
3 400
.
.
.
I need to get this output:
OrderID TotalWeight TempTotalWeight
====== ========== ==============
1 100 100 ( 0 + 100 )
2 150 250 ( 100 + 150 )
3 400 650 ( 100 + 150 + 400 )
How to write the sql statement for it with the best performance ( I have a lot of Data )?
ASKER
Thank you for your reply.
I need it in a "Select" statement as I can't create tables for that.
I need it in a "Select" statement as I can't create tables for that.
the simple one is this, but I will try to see if can have something better (for performance)
SELECT
OrderID,
TotalWeight,
(SELECT SUM(TotalWeight) FROM Orders B WHERE B.OrderID <= A.OrderID) TempTotalWeight
FROM Orders A
ORDER BY OrderID
SELECT
OrderID,
TotalWeight,
(SELECT SUM(TotalWeight) FROM Orders B WHERE B.OrderID <= A.OrderID) TempTotalWeight
FROM Orders A
ORDER BY OrderID
select OrderId, O.TotalWeight ,(select sum(TotalWeight) from Orders where OrderID <= O.OrderID) ''Running TotalWeight' from Orders O
sorry corrected:-
select OrderId, O.TotalWeight ,(select sum(TotalWeight) from Orders where OrderID <= O.OrderID) 'Running TotalWeight' from Orders O
ASKER
I have tested this query against my table, it works but its performance is bad :(
I have now around 13000 rows in the table & it took around 39 sec to complete the query!!
I am looking for faster select statement.
Any other suggestions?
I have now around 13000 rows in the table & it took around 39 sec to complete the query!!
I am looking for faster select statement.
Any other suggestions?
try using computed column for running total:-
http://davidfrommer.blogsp ot.com/200 8/08/using -computed- column-to- calculate. html
http://davidfrommer.blogsp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>I have tested this query against my table, it works but its performance is bad <<
Not surprising. Typically the presentation layer handles running totals and not SQL Server. You may get slightly better performance using a CTE, but I would not hold you breath. Simply put, this is a bad idea.
Not surprising. Typically the presentation layer handles running totals and not SQL Server. You may get slightly better performance using a CTE, but I would not hold you breath. Simply put, this is a bad idea.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you all for suggestions.
Using the temp table gave me great results & also using the Self join query from this post http://davidfrommer.blogspot.com/2008/08/using-computed-column-to-calculate.html
but the temp table was better.
Using the temp table gave me great results & also using the Self join query from this post http://davidfrommer.blogspot.com/2008/08/using-computed-column-to-calculate.html
but the temp table was better.
reftable (totalweight int , temptotalweight varchar(50))
insert into reftable values(100, '100 (0+100))
insert into reftable values(150, '250 (100+150))
...