Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:01,990 --> 00:00:08,380
Now, in the last lecture we discussed about the sum function, or you can say total function.
2
00:00:09,550 --> 00:00:13,330
Now there is another variation of this function.
3
00:00:14,550 --> 00:00:17,400
Through which you can find the running total.
4
00:00:17,760 --> 00:00:19,560
Now, what is running total?
5
00:00:19,860 --> 00:00:26,970
So suppose for see when you have three kinds of order with revenue, 100, 203 hundred.
6
00:00:27,890 --> 00:00:32,240
Now, if you calculate the total, the total will be 600.
7
00:00:33,550 --> 00:00:41,710
But a running total will only add that value and only the values that are above that value.
8
00:00:42,160 --> 00:00:47,410
For example, here in the first row, the revenue is 100.
9
00:00:47,440 --> 00:00:51,100
There is nothing above it, so it will only give 100.
10
00:00:51,730 --> 00:00:59,230
But in the second row the revenue is 200 and there is a revenue of 100 before that.
11
00:01:00,280 --> 00:01:04,630
So the total will be 300 for the third row.
12
00:01:04,660 --> 00:01:11,170
The revenue is 300 and there is already a revenue of 201 hundred above that.
13
00:01:11,440 --> 00:01:15,130
So the running total will be 600.
14
00:01:15,610 --> 00:01:20,920
So running total is calculating the sum up to that point.
15
00:01:21,760 --> 00:01:26,590
Usually running totals are calculated on date values.
16
00:01:29,170 --> 00:01:35,800
For example, you may have heard of terms like year till date or month till date.
17
00:01:37,320 --> 00:01:42,210
Which correspond to total number of sales till that day.
18
00:01:43,260 --> 00:01:50,280
So running total is very useful when we are working with revenues and sales figures.
19
00:01:53,050 --> 00:01:55,540
Now how to calculate running total.
20
00:01:56,270 --> 00:02:02,510
You can do that just by adding order by and to your some function.
21
00:02:03,700 --> 00:02:11,950
So earlier we were only using partition by if we write order by statement as well, the sum function
22
00:02:11,950 --> 00:02:14,320
will calculate the running total.
23
00:02:16,120 --> 00:02:22,450
So when we are finding total sum, we just have to use partition by and it will calculate the total
24
00:02:22,450 --> 00:02:22,900
sum.
25
00:02:24,430 --> 00:02:30,400
If you want to calculate the running total, just add order by with your sum function.
26
00:02:31,540 --> 00:02:37,510
So here in this case, I want my running total to be calculated according to the date.
27
00:02:37,690 --> 00:02:41,830
So you just have to mention ordered by end date.
28
00:02:43,580 --> 00:02:46,250
Now let's go back to our PG admin.
29
00:02:48,780 --> 00:02:56,550
So here in our previous example, we calculated the total sales of each estate.
30
00:02:56,790 --> 00:03:03,750
Now let's calculate the running total of each estate according to the order of date.
31
00:03:05,400 --> 00:03:07,290
The formula will remain the same.
32
00:03:07,290 --> 00:03:11,580
So I will just copy paste it here and then.
33
00:03:13,270 --> 00:03:16,330
And then I'll write some of.
34
00:03:17,470 --> 00:03:18,520
Sales.
35
00:03:20,000 --> 00:03:24,440
Over the partition by will remain the same.
36
00:03:32,890 --> 00:03:39,730
And this time we want to calculate running total according to the order of the order date.
37
00:03:40,930 --> 00:03:43,600
Order by the column name is Order date.
38
00:03:51,310 --> 00:03:53,990
We are using a year of running total year.
39
00:03:56,600 --> 00:04:00,030
If I run this so you will see the difference.
40
00:04:00,050 --> 00:04:02,660
Currently we are only getting total.
41
00:04:05,600 --> 00:04:06,890
I have to put.
42
00:04:07,680 --> 00:04:08,670
When the score.
43
00:04:10,860 --> 00:04:12,000
Let's run this.
44
00:04:13,750 --> 00:04:16,780
You can see that now we are getting the running total.
45
00:04:16,990 --> 00:04:19,840
Our data is ordered by date.
46
00:04:19,960 --> 00:04:28,540
So first we have the Alabama state, for which we have all the orders and the ascending order of order
47
00:04:28,540 --> 00:04:29,120
date.
48
00:04:29,140 --> 00:04:31,840
So this is the order of June 2014.
49
00:04:31,840 --> 00:04:34,090
This the order of May 2014.
50
00:04:34,450 --> 00:04:41,410
Again, you can see that the data is arranged in the ascending order of order date.
51
00:04:42,240 --> 00:04:45,600
Now here we have the running total in the last column.
52
00:04:45,900 --> 00:04:50,100
You can see that for the first order, the sales value was 152.
53
00:04:50,460 --> 00:04:52,600
So the running total is 152.
54
00:04:52,620 --> 00:04:55,980
That is some till this row.
55
00:04:57,330 --> 00:05:00,570
For the second row, the sales is 180.
56
00:05:01,510 --> 00:05:03,800
And the running total is 333.
57
00:05:03,820 --> 00:05:07,880
That is 183.93 plus one 52.64.
58
00:05:07,900 --> 00:05:10,790
So this is the sum of these two values.
59
00:05:10,810 --> 00:05:15,340
Similarly, 1053 is the sum of these three values.
60
00:05:15,520 --> 00:05:20,390
1555 is the sum of these four values and so on.
61
00:05:20,410 --> 00:05:24,730
This will go on till we reach the last value of Alabama.
62
00:05:25,300 --> 00:05:29,080
So if I come here So this is the last value.
63
00:05:29,530 --> 00:05:33,760
Just before that, the running total was 31034.
64
00:05:33,790 --> 00:05:36,910
In the next row, the value was 4.36.
65
00:05:36,970 --> 00:05:44,730
So the running total in the last value is 31038, which is equal to the total value of the sales.
66
00:05:44,740 --> 00:05:49,300
So if you sum all the values till here, this is the sum.
67
00:05:49,450 --> 00:05:53,530
You can also see that in the sales total we are getting the same value.
68
00:05:55,070 --> 00:05:59,000
So running total will calculate the total step by step.
69
00:06:00,320 --> 00:06:01,040
Now.
70
00:06:01,940 --> 00:06:03,050
After Alabama.
71
00:06:03,050 --> 00:06:08,640
We have the data of Arizona State here since we have used partition by state.
72
00:06:08,660 --> 00:06:14,970
The running total will start from zero and it will calculate the running total.
73
00:06:14,990 --> 00:06:20,120
As we have discussed previously and for the last value of Arizona.
74
00:06:20,810 --> 00:06:26,810
Also, the total will be equal to the sales total that we are getting in this column.
75
00:06:29,640 --> 00:06:33,870
You can see that for the last value, the total and the running total is same.
76
00:06:35,100 --> 00:06:40,830
So that's the variation of some function with or without order keyword.
77
00:06:40,860 --> 00:06:47,430
If you use order, you will get the same value in your partition and that will be the total value.
78
00:06:48,120 --> 00:06:54,270
If you are using order by in your sum function, then you will get the running total.
79
00:06:54,300 --> 00:06:55,890
As we have seen here.
80
00:06:56,730 --> 00:06:57,900
That's all for this video.
81
00:06:57,930 --> 00:06:58,470
Thank you.
7364
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.
Afrikaans
Akan
Albanian
Amharic
Arabic
Armenian
Azerbaijani
Basque
Belarusian
Bemba
Bengali
Bihari
Bosnian
Breton
Bulgarian
Cambodian
Catalan
Cebuano
Cherokee
Chichewa
Chinese (Simplified)
Chinese (Traditional)
Corsican
Croatian
Czech
Danish
Dutch
English
Esperanto
Estonian
Faroese
Finnish
Galician
Georgian
German
Greek
Guarani
Haitian Creole
Hausa
Hawaiian
Hebrew
Hmong
Hungarian
Icelandic
Indonesian
Irish
Italian
Japanese
Kazakh
Kinyarwanda
Kirundi
Kongo
Korean
Krio (Sierra Leone)
Kurdish
Kyrgyz
Laothian
Latin
Latvian
Lithuanian
Luganda
Luo
Luxembourgish
Macedonian
Malagasy
Malay
Maltese
Maori
Mauritian Creole
Moldavian
Mongolian
Myanmar (Burmese)
Montenegrin
Nepali
Norwegian
Pashto
Persian
Polish
Portuguese (Brazil)
Portuguese (Portugal)
Quechua
Romanian
Romansh
Russian
Samoan
Scots Gaelic
Serbian
Sesotho
Setswana
Seychellois Creole
Shona
Sindhi
Sinhalese
Slovak
Slovenian
Somali
Spanish
Swedish
Tajik
Thai
Tigrinya
Tonga
Turkish
Turkmen
Ukrainian
Uzbek
Vietnamese
Welsh
Wolof