Create Running Totals Using Window Functions

Brett Cassette
InstructorBrett Cassette
Share this video with your friends

Social Share Links

Send Tweet
Published 8 years ago
Updated 6 years ago

How could your Kickstarter-type site list the running total for each contribution next to it? Using window functions of course! The default behavior for aggregations under window functions is to create running totals.

[00:00] If we have a Kickstarter type site where we have projects and we have backers, and these backers are contributing to the projects, we have a list of each of their contributions. Maybe we want to show a running total of the contributions. We can do that with a sum function, sum of the contributions.

[00:23] We use our over clause to partition by the project ID. As we've seen in the past, if we don't include any kind of order clause, now the sum is going to be the same for the entire row, or for the entire window, and that's not what we want here. We'll have to have some kind of order by here, so we can say, "Order by contribution," which, by default, will be ascending.

[00:46] We see we start with 5.5, add 25. It gives us 30.5, etc., etc., for the entire window. This also works fine if we want to do it as a descending. We just have to make sure to include the order by, or some sort of explicit frame clause, which we'll talk about next.

egghead
egghead
~ 34 minutes ago

Member comments are a way for members to communicate, interact, and ask questions about a lesson.

The instructor or someone from the community might respond to your question Here are a few basic guidelines to commenting on egghead.io

Be on-Topic

Comments are for discussing a lesson. If you're having a general issue with the website functionality, please contact us at support@egghead.io.

Avoid meta-discussion

  • This was great!
  • This was horrible!
  • I didn't like this because it didn't match my skill level.
  • +1 It will likely be deleted as spam.

Code Problems?

Should be accompanied by code! Codesandbox or Stackblitz provide a way to share code and discuss it in context

Details and Context

Vague question? Vague answer. Any details and context you can provide will lure more interesting answers!

Markdown supported.
Become a member to join the discussionEnroll Today