Home » SQL & PL/SQL » SQL & PL/SQL » SUM OVER
SUM OVER [message #195673] Sat, 30 September 2006 22:38 Go to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
How do I use the Sum Over approach to accumulate a numeric field, but have it restart when a certain field changes, like EMPLID?

SUM (SCORE) OVER (ORDER BY TEST_ID) "ACCUM"

But I want it to restart with every new EMPLID...

Thanks in advance!
Re: SUM OVER [message #195674 is a reply to message #195673] Sat, 30 September 2006 23:13 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member

SUM (SCORE) OVER (PARTITION BY emplid ORDER BY TEST_ID) "ACCUM"

Re: SUM OVER [message #195676 is a reply to message #195673] Sun, 01 October 2006 00:04 Go to previous messageGo to next message
Matthew Waugh
Messages: 71
Registered: October 2004
Member
Thanks Barbara....I assume that if I wanted to restart the accumulation when 2 fields change, the following would work:

SUM (SCORE) OVER (PARTITION BY paygroup,file_nbr ORDER BY TEST_ID) "ACCUM"

The idea being that 2 people can have the same file_nbr, but they can't have the same paygroup & file_nbr....

Will the above achieve what I'm looking for?



Re: SUM OVER [message #195720 is a reply to message #195676] Sun, 01 October 2006 18:02 Go to previous message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
Yes, and you can always verify that by testing.
Previous Topic: Merge Statement
Next Topic: convert From week to date
Goto Forum:
  


Current Time: Sun Dec 11 06:33:22 CST 2016

Total time taken to generate the page: 0.11849 seconds