Time
9 hours 41 minutes
Difficulty
Intermediate
CEU/CPE
10

Video Transcription

00:00
Hi. Welcome back to Module six. This is less than 6.8 running totals. The the running total column is a very common report request common enough that I decided to include it as a lesson because depending on what version of Sequels urine, it's not always straightforward how you generate a running total.
00:17
So let's go ahead and take a look at these bullet points again. A running total is a common report request. If you write many sequel reports, you will run into the need for a running total eventually.
00:29
Now a running total is a column that, as the previous record totals, as the report generates,
00:35
window functions make running totals much easier to deal with. Without the window functions, you typically have to do some additional sequel gymnastics to get the running total working the way that you want it to work.
00:47
So again, the running total was much more difficult and older versions of sequel.
00:53
Interestingly, Microsoft Sequel into introduced The Window functions in Microsoft sequel 2005 however, and my sequel, they weren't introduced until my Simple eight in 2018 which is fairly recently.
01:04
Now, with all that out of the way. Let's go ahead and head to the development environment. Take a look at what this looks like.
01:11
I welcome back to the development environment, went ahead and pulled up the operator documentation at the My sequel website. If you're ever looking at some sequel code in my sequel and you see an unfamiliar character being used in what you derived to be some kind of special way, one thing you might check is the operators documentation.
01:29
And I bring this up because we're gonna be focused on this guy right here, which is that we're looking assignment operator. In my opinion, we know that's the assignment operator because it assigns a value,
01:40
and we'll have to use this operator to perform the older method of getting a running total. Now. With that said, let's go ahead and take a look at our query.
01:49
So I have the common table expression that we generated in the union lesson.
01:55
But I don't have the selection query,
01:57
so we'll go ahead and start with writing the older way of writing running totals. So I'm gonna write a select
02:05
breath number
02:07
comma
02:07
Andi, I'm going to get the amount so that I can check that I have a a good running total when I haven't done something wrong.
02:15
And here's where things start to get a little interesting. I want to make a variable
02:21
at running
02:23
total that at symbol and the kids that were starting a variable,
02:28
I'm gonna sign a value that value is going to be itself
02:35
plus amount
02:37
as
02:38
cumulative
02:40
some.
02:42
All right,
02:44
we're gonna select that from the query or the common table expression.
02:49
Now, here's where things get a little interesting. Again, we're gonna join
02:53
on a selection
02:57
of the at running total variable
03:02
the assignment operator starting at zero.
03:07
And we're going to order by
03:09
the ref number.
03:12
We'll go ahead and limit by three. Initially, just to make sure that we're not overwhelmed with results
03:19
wouldn't run that.
03:21
Okay, so a drive table must, of course, have an alias. So rt running total
03:29
and we can see that we have the regular amount. Here
03:31
we add that up. We have the running total ending over here on the very right,
03:37
so it looks like the running total is working correctly.
03:39
Now we review the way this is implemented. We can see that this looks a little different than the quarries were probably used to writing and that we're declaring a variable were signing that variable to itself, plus the amount column. And then the really interesting part is that were then joining to that variable as its own derived table.
03:59
So in larger queries that could get, uh, confusing to look at, I definitely prefer the new method. So let's go ahead and take a look at the window functions and how that would look. So I'm gonna go ahead and erase this.
04:11
I'm gonna start writing out the way that we would handle this within new window functions.
04:16
So select rough number
04:20
never bring in the amount as well.
04:25
And then I'm going to some the amount
04:30
over, and that's starting that window function.
04:33
I'm gonna order by
04:35
breath number
04:41
as running
04:42
total
04:45
Andi from Query.
04:48
That's limit three again.
04:50
Let's run that
04:54
and we got the same results, and I believe this is, and I think you'll agree to that. This is easier to read. We don't have that weird join on the variable going on, and we're not having to declare a variable. Were just using this new function the over in conjunction with the order by to Generate are running total.
05:11
So this becomes more or less just getting used to using the window functions to generate the running totals in the way that you want to.
05:17
Now let's go out and get rid of this limit three
05:20
and run all that.
05:23
So now we have a running total all the way to that total line that we had in the previous lesson. And, of course, if we wanted to add the total line, that be easy. We just write a union in our query and add that total line in,
05:35
and that completes this lesson on running totals.
05:39
Let's go ahead and head over and take a look at the summary,
05:43
and that brings us to the 6.8 summary
05:46
and this lesson. We added a running total to our previous union query.
05:49
We reviewed the old method and the new improved method for generating a running total.
05:55
And of course, I recommend the new method whenever possible. The only time it's really not possible is if you happen to be saddled with an old version of sequel that simply cannot be changed for some legacy reason
06:08
and of course, the reason is it's just simply easier to deal with.
06:12
So that completes this lesson, and I hope to see in the next thank you.

Up Next

Intermediate SQL

This free course introduces the student to intermediate concepts found in the implementation and application of Structured Query Language (SQL) within professional business environments.

Instructed By

Instructor Profile Image
Kitt Parker
Instructor