Would you like to inspect the original subtitles? These are the user uploaded subtitles that are being translated:
1
00:00:00,690 --> 00:00:04,980
Now the next function we are going to discuss is total function.
2
00:00:05,610 --> 00:00:11,340
Total also works works like count and average function.
3
00:00:12,150 --> 00:00:19,620
It will create a new column containing a sum of values within a window frame or within your partition.
4
00:00:20,400 --> 00:00:26,730
So again, suppose we have two different kinds of customers C one and C two.
5
00:00:26,850 --> 00:00:33,240
Now C one and C two are placing multiple orders at different dates.
6
00:00:35,030 --> 00:00:38,600
And we also have the order value with us.
7
00:00:38,780 --> 00:00:47,510
Now, if I want to calculate the total order value of C one and C two to be placed in front of each
8
00:00:47,510 --> 00:00:50,090
row, I can use the total function.
9
00:00:50,210 --> 00:00:59,540
For example, if C one is ordering three times with the revenue of 100, 203 hundred, I want a total
10
00:00:59,540 --> 00:01:04,010
of 600 to be written in front of all the rows of C one.
11
00:01:04,760 --> 00:01:15,290
So on first of Jan 2022, 11th of May 2022 and 25th of August 2022, I want 600 return to be in front
12
00:01:15,290 --> 00:01:16,790
of each of this row.
13
00:01:17,720 --> 00:01:24,920
Similarly, C two is placing order four times with value 300, 300, 204 hundred.
14
00:01:25,400 --> 00:01:27,740
The total is 1200.
15
00:01:27,740 --> 00:01:33,800
So I want 1200 to be written in front of each row of customer C to.
16
00:01:34,860 --> 00:01:36,260
Now how to do that?
17
00:01:36,270 --> 00:01:39,240
We can use the some window function.
18
00:01:39,600 --> 00:01:42,600
We just have to write some and then bracket.
19
00:01:42,600 --> 00:01:47,150
We have to write on what column we want the sum to be calculated.
20
00:01:47,160 --> 00:01:49,770
So here we want the sum on revenue.
21
00:01:50,370 --> 00:01:57,570
So we have to write revenue and again, we have to write keyword of over and partition by and partition
22
00:01:57,570 --> 00:01:58,080
by.
23
00:01:58,170 --> 00:02:04,650
We have to specify how to group our data for calculating this total.
24
00:02:05,040 --> 00:02:09,630
So here in this case we are partitioning on customer.
25
00:02:09,630 --> 00:02:11,220
So we have to write customer.
26
00:02:11,670 --> 00:02:20,160
Now let's calculate the total of each state in front of each order id.
27
00:02:20,700 --> 00:02:31,680
So I want a table which contains order IDs date of that order, and then a state from which that order
28
00:02:31,680 --> 00:02:40,200
was placed, and then another column of total value of orders from that state.
29
00:02:40,830 --> 00:02:43,290
So let's see how to do that.
30
00:02:46,030 --> 00:02:48,850
Now first, let's see the order table.
31
00:02:48,880 --> 00:02:51,340
So I'll write total.
32
00:02:52,370 --> 00:02:53,390
You can ignore this.
33
00:02:53,390 --> 00:02:55,550
This is just the commenting part.
34
00:02:56,630 --> 00:02:59,990
So select the star from.
35
00:03:00,690 --> 00:03:04,380
So it's first, let's understand this table.
36
00:03:09,740 --> 00:03:12,570
Here we have a unique key of order line.
37
00:03:12,590 --> 00:03:18,200
Then we have order I.D. and then order date corresponding to that order ID.
38
00:03:18,530 --> 00:03:22,520
We also have customer ID corresponding to that order ID.
39
00:03:22,550 --> 00:03:25,640
We don't have the state data right now.
40
00:03:26,030 --> 00:03:28,550
And we also have the sales data here.
41
00:03:31,010 --> 00:03:37,790
So now first what we are going to do is we will roll up this data on order ID level.
42
00:03:38,450 --> 00:03:41,800
So currently order ID is not unique.
43
00:03:41,810 --> 00:03:45,560
There can be multiple rows of same order ID.
44
00:03:45,800 --> 00:03:53,000
This is happening because if the product ID is different, we are getting multiple lines of that order.
45
00:03:53,000 --> 00:04:01,490
ID is split ID according to the product ID since we only concerned with our revenue and customer data
46
00:04:01,490 --> 00:04:05,030
here, will roll it up to order ID.
47
00:04:06,560 --> 00:04:07,850
So how to do that?
48
00:04:07,850 --> 00:04:10,610
Select order ID.
49
00:04:13,140 --> 00:04:15,330
Then we want to date.
50
00:04:15,450 --> 00:04:22,080
Date is unique to authority, so it will always be same within each order.
51
00:04:23,250 --> 00:04:26,400
So we also want order date here.
52
00:04:26,610 --> 00:04:33,210
But since we will be grouping this data on order ID, we have to use the aggregate function on order
53
00:04:33,210 --> 00:04:33,780
date.
54
00:04:34,710 --> 00:04:38,580
So in such cases you can just use max order function.
55
00:04:41,200 --> 00:04:42,310
It will do nothing.
56
00:04:42,310 --> 00:04:46,060
It will just get the maximum date out of this order.
57
00:04:47,980 --> 00:04:50,380
I will name it as order date only.
58
00:04:52,040 --> 00:05:00,020
I also want customer ID and I also know that there is only one customer ID belonging to an order ID
59
00:05:00,050 --> 00:05:03,260
There can be multiple customer IDs for a same order.
60
00:05:04,220 --> 00:05:11,960
But again, since I am grouping this data on order ID only have to use the aggregate function.
61
00:05:12,500 --> 00:05:15,620
Again, I will be using a max aggregate function.
62
00:05:15,620 --> 00:05:19,370
Max also works on a string type of data.
63
00:05:19,400 --> 00:05:24,230
It will give you one of the multiple values that you have for customer ID.
64
00:05:25,100 --> 00:05:29,000
I'm just using Max of customer ID as customer ID.
65
00:05:33,230 --> 00:05:38,780
And then most importantly, we want the total sales.
66
00:05:39,380 --> 00:05:44,180
So we'll write some off sales.
67
00:05:45,850 --> 00:05:49,830
As revenue or.
68
00:05:50,930 --> 00:05:51,680
As.
69
00:05:52,810 --> 00:05:53,860
Sales.
70
00:05:55,950 --> 00:05:56,760
From.
71
00:05:57,910 --> 00:05:58,930
Sales stable.
72
00:06:02,850 --> 00:06:04,140
Let's run this.
73
00:06:06,320 --> 00:06:10,610
We forget to add group by close group by.
74
00:06:12,630 --> 00:06:13,560
Moderately.
75
00:06:17,370 --> 00:06:18,600
Let's run this.
76
00:06:20,450 --> 00:06:26,510
Now you can see that my data is rolled up to other ID level.
77
00:06:26,660 --> 00:06:31,790
I have the order ID and the sum of sales from that order ID.
78
00:06:31,850 --> 00:06:35,450
I also have ordered it and customer ID of that order.
79
00:06:38,110 --> 00:06:42,310
Now let's store this data into a table called.
80
00:06:46,900 --> 00:06:50,500
S order to roll up.
81
00:06:58,200 --> 00:07:04,770
Here we have the order ID or the date customer ID and the total sales from that order.
82
00:07:08,340 --> 00:07:09,810
Let's create this.
83
00:07:14,470 --> 00:07:16,900
So we have created this order rollup.
84
00:07:17,350 --> 00:07:22,180
Now, let's add the information of state in this order.
85
00:07:22,180 --> 00:07:23,050
Rollup table.
86
00:07:25,030 --> 00:07:30,100
So we will write Create table order.
87
00:07:33,530 --> 00:07:34,520
Roll up.
88
00:07:36,420 --> 00:07:38,280
The state as.
89
00:07:40,340 --> 00:07:49,980
Select will put this order rollup table as our left table or a table and the customer table as our B
90
00:07:50,030 --> 00:07:57,530
table will write a dot star comma B dot state.
91
00:07:59,560 --> 00:08:00,250
From.
92
00:08:02,450 --> 00:08:02,960
Order.
93
00:08:02,960 --> 00:08:03,770
Roll up.
94
00:08:06,810 --> 00:08:08,190
As a.
95
00:08:09,690 --> 00:08:10,140
Left.
96
00:08:10,140 --> 00:08:10,830
Join.
97
00:08:13,240 --> 00:08:14,260
Customer.
98
00:08:15,430 --> 00:08:16,150
Yes.
99
00:08:16,480 --> 00:08:17,200
Be.
100
00:08:18,290 --> 00:08:18,980
On.
101
00:08:19,940 --> 00:08:20,990
He dot.
102
00:08:21,930 --> 00:08:23,130
Customer ID.
103
00:08:25,070 --> 00:08:29,480
Equal to B dot customer ID.
104
00:08:33,500 --> 00:08:34,730
Let's run this.
105
00:08:36,620 --> 00:08:39,050
You can see that our query is successful.
106
00:08:39,260 --> 00:08:41,510
Let's look at our order rollup.
107
00:08:43,110 --> 00:08:44,040
State table.
108
00:08:47,150 --> 00:08:49,190
I will correct this name as well.
109
00:08:52,390 --> 00:08:53,770
Let's run this again.
110
00:08:54,190 --> 00:08:54,580
Yeah.
111
00:08:59,600 --> 00:09:01,880
Lets view the data.
112
00:09:02,960 --> 00:09:07,190
Select start from this.
113
00:09:08,590 --> 00:09:11,560
If I run this, you can see that.
114
00:09:11,560 --> 00:09:14,710
Now we have the estate column as well.
115
00:09:14,710 --> 00:09:15,670
And this data.
116
00:09:17,580 --> 00:09:24,630
Now, as I said, we wanted to have another column here which will contain the total sales value of
117
00:09:24,630 --> 00:09:26,010
this state.
118
00:09:28,310 --> 00:09:30,140
So how can we do that?
119
00:09:30,500 --> 00:09:32,210
We can select.
120
00:09:34,330 --> 00:09:36,340
A star, comma.
121
00:09:39,000 --> 00:09:40,170
We'll write some.
122
00:09:40,920 --> 00:09:43,080
This is our aggregate window function.
123
00:09:43,440 --> 00:09:45,630
Now we want the sum of sales.
124
00:09:47,130 --> 00:09:50,160
Sum of sales we have to write over.
125
00:09:50,190 --> 00:09:51,480
Or what is our keyword?
126
00:09:51,510 --> 00:09:53,040
Then bracket is start.
127
00:09:53,760 --> 00:09:56,310
Partition by is another keyword.
128
00:09:57,150 --> 00:10:06,660
Now here, since we want to calculate the sum of all the sales in a particular estate, so here I will
129
00:10:06,660 --> 00:10:08,370
partition by estate.
130
00:10:11,850 --> 00:10:14,880
Now we have to give Alias.
131
00:10:15,870 --> 00:10:19,440
We want to save this column as sales.
132
00:10:21,180 --> 00:10:22,020
State.
133
00:10:23,190 --> 00:10:23,790
Total.
134
00:10:25,850 --> 00:10:26,690
From.
135
00:10:28,560 --> 00:10:30,680
You want this data from this table.
136
00:10:31,880 --> 00:10:33,320
Now let's run this.
137
00:10:37,320 --> 00:10:40,380
You can see that now we have the sales.
138
00:10:41,260 --> 00:10:43,240
Total of each state.
139
00:10:43,570 --> 00:10:48,250
For example, for Alabama, we have these many other IDs.
140
00:10:50,730 --> 00:10:59,040
And the total of sales from Alabama is this one 31,038 units.
141
00:11:00,380 --> 00:11:04,970
Similarly, we have data of other states as well.
142
00:11:08,710 --> 00:11:12,610
So that's how we can use some Windows function and SQL.
11095
Can't find what you're looking for?
Get subtitles in any language from opensubtitles.com, and translate them here.