SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Running Total SQL2012
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 05/09/2013 :  10:07:49  Show Profile  Reply with Quote
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

547 Posts

Posted - 05/09/2013 :  11:13:16  Show Profile  Reply with Quote
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)
...

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 05/10/2013 :  00:32:42  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 05/13/2013 :  14:05:03  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 05/13/2013 :  15:21:42  Show Profile  Reply with Quote
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
Go to Top of Page

xhostx
Constraint Violating Yak Guru

USA
277 Posts

Posted - 05/13/2013 :  16:23:44  Show Profile  Reply with Quote
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
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3724 Posts

Posted - 05/13/2013 :  17:19:34  Show Profile  Reply with Quote
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.
Go to Top of Page

worldtraveler
Starting Member

1 Posts

Posted - 06/06/2013 :  01:49:03  Show Profile  Reply with Quote
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.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 06/06/2013 :  02:08:17  Show Profile  Reply with Quote
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.




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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000