Link to home
Start Free TrialLog in
Avatar of TaibaDXB
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 )?
Avatar of Aaron Shilo
Aaron Shilo
Flag of Israel image

create a reference table

reftable (totalweight int , temptotalweight varchar(50))

insert into reftable values(100, '100 (0+100))
insert into reftable values(150, '250 (100+150))
...

Avatar of TaibaDXB
TaibaDXB

ASKER

Thank you for your reply.
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, O.TotalWeight ,(select sum(TotalWeight) from Orders where OrderID <= O.OrderID) ''Running TotalWeight' from Orders O

Open in new window

sorry corrected:-

select OrderId, O.TotalWeight ,(select sum(TotalWeight) from Orders where OrderID <= O.OrderID) 'Running TotalWeight' from Orders O

Open in new window

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?
ASKER CERTIFIED SOLUTION
Avatar of JoeNuvo
JoeNuvo
Flag of Viet Nam image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
>>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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.