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.
| Author |
Topic |
|
mahesh_bote
Constraint Violating Yak Guru
298 Posts |
 Posted - 2006-05-02 : 07:02:42
|
| hi,can i get runing total without formula if i use emp table and i need sal column runing total without formula which tool or which function i useplease give me idea or if use formula then how i use formula column and placeholder column and where i put both colume.g. EMPNO SAL SUM(SAL)OVER(ORDERBYEMPNO)---------- ---------- -------------------------- 7369 800 800 7499 1600 2400 7521 1250 3650BMahesh |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2006-05-02 : 07:13:44
|
"can i get runing total without formula"Yes. Do it in your front end application. It is much easier that way.select EMPNO, SAL, (select sum(SAL) from EMP x where x.EMPNO <= e.EMPNO) as RUNNING_TOTALfrom EMPorder by EMPNO
KH |
 |
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
Posted - 2006-05-02 : 07:44:26
|
| Where do you want to show the running Total?Use Reports and its Running Total feature to show running total. Trying through sql is ineffecientMadhivananFailing to plan is Planning to fail |
|
|
|
mmarovic
Aged Yak Warrior
518 Posts |
Posted - 2006-05-03 : 03:53:38
|
| Madhivanan is right, the running total calculation is not something should be done in sql. It is inefficient and it is not what sql is supposed to do. SQL should return raw data without formating, client code such as vb or asp or c# shoud take care of formating. Running total is special case where you have an agregate information that are displayed at row level but you have dependences on more then one previous rows. When I say previous it means aggregation is dependant on row order. Tables and sql rowsets are structure that don't "promise" any row order. To have data order you have to include order by clause, but it only applies to reading data (select statetment). All other DMS assumes you tell what you want to do, not how to do it, which aslo means you don't specify the order of updates/inserts/deletes. The only official way designed to let you change data in specific order are loops where you update one row at time. SQL is great at data set processing and slow with row-by-row processing. So the conclusion is: calculate running total on the client.I wrote recently article about solving running totals problem, so you can see there a few sql solutions, but the final recommendation is to do it on the client. Article link: http://www.sql-server-performance.com/mm_cursor_friendly_problem.aspRead also discussion related to article, there you will find a link showing that one of solution suggested doesn't work on mssql server 2005. |
|
|
|
|
|
|
|
|
Running Total