Running Total SQL2012 - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Running Total SQL2012

Author

Topic

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-05-09 : 10:07:49

Im trying to calculate a running total for a column "Total". This is an example I build following Microsoft website...however, its throwing an erros. Can anyone help please?

Thanks!
selct sum(total)
, sum(total) over (partition by p_id rows unbounded preceding)
from table

--------------------------
Joins are what RDBMS's do for a living

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-05-09 : 11:13:16

You need a grouping criteria for the first sum() and an order by clause in the second sum()

post some of your data and expected results if you need more help.
example:



SELECT SUM(AMT) over (partition by CUSTOMERID  order by PAIDDATE rows unbounded preceding)
...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-10 : 00:32:42

it needs to determine the order in which it has to sequence the records within partition for calculating the running sum. Thats why it required an ORDER BY bafore specifying the row boundaries

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-05-13 : 14:05:03

here's what I have now, still getting an error:

Incorrect syntax near 'rows'.

sltct sum(amt), sum(amt) over ( partition by policy order by claim rows s unbounded preceding) as 'Running total'
from TableX
any hits please?

Thanks!

--------------------------
Joins are what RDBMS's do for a living

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-13 : 15:21:42

Mumu's query works correctly for me. In your latest post, there are couple of typos in the query.

Also, if you are trying to list the sum for each policy in the first summation, you need an over clause for that as well:

select 
	sum(amt) over(partition by policy), 
	sum(amt) over ( partition by policy order by claim rows unbounded preceding) as 'Running total'
from TableX

xhostx
Constraint Violating Yak Guru

277 Posts

Posted - 2013-05-13 : 16:23:44

Thanks James! but remember that I have dozens of other fields Im selecting...meaning that there's some extra fields/group by....It still didn't work for me.

--------------------------
Joins are what RDBMS's do for a living

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-05-13 : 17:19:34

In general, if you have any columns in your select list that are outside of aggregate functions or in window functions (which loook like aggregate functions followed by an OVER clause) all such columns must be included in the group by clause. If you can post a simplified example with the group by, it would be easier to explain the rule.

If I change the example above to remove the OVER clause on the first sum, then I would need a grop by over all columns in the second sum and in the partition by and order by clauses (amt, policy, and claim). But when you do that, the first summation would not really be doing any summation.

I know that sounds confusing when I say it; if you post a simplified example that gives you trouble, it would be easier to explain.

worldtraveler
Starting Member

1 Post

Posted - 2013-06-06 : 01:49:03

Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.

SELECT T1.SL,
T1.GroupName,
T1.Amount,
SUM(T2.Amount) as CumulativeSum
FROM @Temp T1 INNER JOIN
@Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount
ORDER BY T1.SL

for more with example you can visit: http://cybarlab.blogspot.com/2013/06/cumulative-sum-in-sql.html

Hope it will help you.

[url][/url]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-06 : 02:08:17

quote:
Originally posted by worldtraveler

Some time we need to do cumulative sum or running total in a table. In SQL we can easily do it. The following code will help us.

SELECT T1.SL,
T1.GroupName,
T1.Amount,
SUM(T2.Amount) as CumulativeSum
FROM @Temp T1 INNER JOIN
@Temp T2 on T1.SL >= T2.SL
GROUP BY T1.SL,T1.GroupName, T1.Amount
ORDER BY T1.SL

for more with example you can visit: http://cybarlab.blogspot.com/2013/06/cumulative-sum-in-sql.html

Hope it will help you.

[url][/url]

make sure you read this

http://www.sqlservercentral.com/articles/T-SQL/61539/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources