1 00:01.430 --> 00:01.880 Okay. 2 00:01.880 --> 00:06.350 So let's start with the Windows Aggregate function in SQL. 3 00:06.440 --> 00:13.910 So this is windows aggregate functions are basically used for the running total as well as percentage 4 00:13.910 --> 00:15.230 out of total. 5 00:15.380 --> 00:25.610 Means suppose some salary are there right 1% salary is ₹2,000 and all the employees salary are one lakh 6 00:25.610 --> 00:26.060 rupees. 7 00:26.060 --> 00:31.940 So how much 2000 is earning from what percentage out of total? 8 00:31.970 --> 00:37.790 So that kind of calculation, if we want to do then we can use the windows function. 9 00:38.570 --> 00:46.400 They can be used to aggregate rows in a defined window in windows aggregate aggregate function. 10 00:46.400 --> 00:51.650 We can all three clauses in over clause but nothing is mandatory. 11 00:51.650 --> 00:54.170 So what are the that three clauses? 12 00:54.680 --> 00:56.330 One we have seen yesterday. 13 00:56.330 --> 01:03.200 That is what partitioned by clause To group the data based upon some partition, right? 14 01:03.770 --> 01:07.280 Then another thing is we can use the order by clause. 15 01:08.240 --> 01:10.010 Order by clause, and one can. 16 01:10.040 --> 01:12.740 One thing we can use is the frame clause. 17 01:13.760 --> 01:14.300 Right. 18 01:14.300 --> 01:21.500 So all these three clause we can use in the windows aggregate function but nothing are mandatory over 19 01:21.500 --> 01:22.130 here. 20 01:23.090 --> 01:25.190 Nothing are mandatory. 21 01:25.220 --> 01:27.920 Now let me write the query. 22 01:35.720 --> 01:37.130 Let me write the query. 23 01:37.160 --> 01:40.220 Select star from EMP. 24 01:40.910 --> 01:44.120 Now let me see the data. 25 01:48.890 --> 01:52.250 And copy the data again into the Excel file. 26 02:02.670 --> 02:03.720 Okay. 27 02:04.380 --> 02:05.760 Now suppose. 28 02:09.360 --> 02:11.190 This time, what is my requirement? 29 02:11.190 --> 02:16.650 I want one additional column over here where I want to print. 30 02:16.680 --> 02:17.400 Grand. 31 02:18.420 --> 02:18.960 Grand. 32 02:18.960 --> 02:20.190 Total of salary. 33 02:20.220 --> 02:20.850 Right? 34 02:20.880 --> 02:21.480 Grand. 35 02:21.510 --> 02:22.680 Total of salary. 36 02:22.980 --> 02:27.060 So what is our grand total of salary over here, we can say. 37 02:29.880 --> 02:32.070 This one is the grand total, right? 38 02:32.190 --> 02:36.090 All of the employee salary is grand total. 39 02:36.330 --> 02:39.300 Let me try to. 40 02:43.500 --> 02:43.800 Okay. 41 02:43.800 --> 02:45.720 Let me write it manually. 42 02:46.680 --> 02:50.700 37595. 43 02:50.730 --> 02:51.390 Right. 44 02:53.400 --> 02:56.340 So it should be across all the rows. 45 02:56.730 --> 03:01.080 Across all the rows, and I want to do some calculation later on. 46 03:01.470 --> 03:02.940 Or I just want to. 47 03:03.060 --> 03:06.570 Display all the grant salary in one additional column. 48 03:06.570 --> 03:10.020 So at that time I can write a query like this. 49 03:10.290 --> 03:13.950 Select star comma sum of salary over. 50 03:14.580 --> 03:15.780 Sum of salary over. 51 03:15.780 --> 03:22.770 So here we have not either done the partition by clause or also we have not done the order by clause. 52 03:22.920 --> 03:25.200 Still, this query will work. 53 03:25.230 --> 03:27.300 Still this query will work. 54 03:27.300 --> 03:32.730 So see what is printed over here in all the rows 37595. 55 03:32.760 --> 03:33.390 Right? 56 03:33.660 --> 03:38.880 This is my grand total of all the salaries and why it is showing the grand total. 57 03:38.910 --> 03:42.540 Because we have not mentioned over here partition by clause. 58 03:42.540 --> 03:50.790 So it will consider all the rows, all the rows of this particular table as one partition that we have 59 03:50.790 --> 03:51.540 seen yesterday. 60 03:51.540 --> 03:57.360 Right now we can do the same thing with the group by clause. 61 03:57.960 --> 03:58.680 Sorry. 62 03:58.680 --> 03:59.970 With the aggregate function. 63 03:59.970 --> 04:04.170 If we do sum of salary then it will give me like this one, right? 64 04:07.710 --> 04:09.720 So that is also the same thing. 65 04:09.720 --> 04:13.830 But over here we cannot able to see every row, right? 66 04:13.830 --> 04:22.650 We are group by or only aggregate function will group entire result set as a one row only entire result 67 04:22.650 --> 04:24.030 set as one row. 68 04:24.030 --> 04:27.660 If we do not mention group by or partition by clause right? 69 04:30.180 --> 04:32.310 Now let's see one more example. 70 04:32.310 --> 04:33.990 Then you will be more clear. 71 04:34.380 --> 04:42.210 Now over here what I am trying to do in this query I want department wise sum of salary, department 72 04:42.210 --> 04:43.530 wise sum of salary. 73 04:43.650 --> 04:48.180 So we have total three departments are there right ten, 20 and 30. 74 04:52.260 --> 04:56.320 We have total three departments are there between 20 and 30. 75 04:56.350 --> 04:58.780 So let me take this data. 76 05:05.320 --> 05:07.150 And paste it over here. 77 05:07.180 --> 05:09.010 So now what is the. 78 05:11.710 --> 05:13.030 Problem over here. 79 05:13.030 --> 05:18.130 We cannot see all other data over here like in the department. 80 05:18.160 --> 05:19.030 Number ten. 81 05:19.060 --> 05:22.750 What are the employees are working in the department number 20. 82 05:22.780 --> 05:25.030 Which are the employees are working. 83 05:25.030 --> 05:27.790 We cannot see that kind of information over here. 84 05:27.790 --> 05:28.420 Right. 85 05:28.420 --> 05:30.730 So that is a problem over here. 86 05:30.730 --> 05:36.760 So to to do to eliminate this problem, what we can do, let me show you. 87 05:36.790 --> 05:39.460 We can do simply like this. 88 05:39.700 --> 05:41.680 We can do sum of salary. 89 05:41.710 --> 05:48.250 Sum of salary over partition by department s department total. 90 05:48.700 --> 05:53.800 So over here in this particular case we what we have seen over here. 91 05:54.070 --> 05:56.260 All the salaries, all the salaries. 92 05:56.260 --> 05:58.300 Some will be there in one column. 93 05:58.300 --> 06:01.270 But what will happen in this particular case? 94 06:01.750 --> 06:02.620 It will. 95 06:02.650 --> 06:04.240 It will do like this. 96 06:08.830 --> 06:09.790 Okay. 97 06:10.630 --> 06:15.490 Select let me do order by over here. 98 06:16.840 --> 06:20.560 Order by department. 99 06:21.010 --> 06:27.400 Now if I do order by over here, then it will give me data in the ordering of department. 100 06:28.030 --> 06:29.050 Department wise. 101 06:29.050 --> 06:29.680 Right. 102 06:31.600 --> 06:34.240 So now let me copy this data. 103 06:36.430 --> 06:37.180 Okay. 104 06:37.180 --> 06:39.190 So if you see over here. 105 06:41.590 --> 06:43.030 Now Department ten. 106 06:43.030 --> 06:47.710 So what it will do it will arrange the Department ten in as one group. 107 06:48.250 --> 06:48.490 Right. 108 06:48.520 --> 06:50.110 Department ten as one group. 109 06:50.110 --> 06:54.710 And then it will give me the total of this particular thing over here. 110 06:54.740 --> 07:03.980 Ten 0150 so ten 10150 over here in this all five rows or all the four, the four rows. 111 07:04.100 --> 07:06.320 Now what it will do over here. 112 07:06.320 --> 07:07.610 Department 20. 113 07:07.640 --> 07:13.280 So 1747517475. 114 07:13.280 --> 07:14.870 It will do department wise right. 115 07:14.900 --> 07:15.530 Right. 116 07:17.990 --> 07:20.360 It will do department wise. 117 07:20.840 --> 07:21.980 Some. 118 07:22.340 --> 07:23.960 And again for 30. 119 07:23.990 --> 07:24.980 What it will do. 120 07:25.010 --> 07:32.630 It will do the sum of this thing 99709970. 121 07:32.630 --> 07:34.520 So let me copy this one. 122 07:35.360 --> 07:38.510 So this is my department number 20. 123 07:45.290 --> 07:51.110 And this will be the data of my department number 30. 124 07:53.360 --> 07:57.830 And let me take this date column into the date format. 125 07:58.370 --> 07:59.060 Okay. 126 07:59.060 --> 08:02.570 So now it will give me the result like this. 127 08:02.570 --> 08:06.020 So in the analysis whatever the requirement will come. 128 08:06.050 --> 08:06.290 Right. 129 08:06.290 --> 08:11.840 We cannot identify which requirement will come at what time. 130 08:11.840 --> 08:14.090 So this is the purpose actually. 131 08:14.120 --> 08:19.820 So let me copy this result set and we can compare. 132 08:20.990 --> 08:23.720 Let me delete this data. 133 08:30.020 --> 08:35.990 So see over here whatever the result we have we have seen over here in the Excel file that. 134 08:39.170 --> 08:42.440 Okay one step above. 135 08:42.500 --> 08:45.650 So that result is is coming over here as well. 136 08:45.680 --> 08:46.310 Right. 137 08:46.640 --> 08:50.960 That same result is coming over here in the salary part. 138 08:52.370 --> 08:54.050 You have to see in the salary part. 139 08:54.080 --> 08:54.740 Right. 140 08:58.250 --> 09:06.440 So this is the use of some of, uh, some aggregate function, then say uh, department wise here order 141 09:06.440 --> 09:06.740 by. 142 09:06.770 --> 09:08.690 If we do order by then also fine. 143 09:08.690 --> 09:11.420 If we do not order by then also fine. 144 09:11.420 --> 09:13.160 It does not affect anything, I think. 145 09:13.190 --> 09:16.040 Order by EMP number. 146 09:16.070 --> 09:17.000 Let's see. 147 09:17.780 --> 09:24.140 It won't affect anything in this particular case, but I think it is giving wrong data. 148 09:24.170 --> 09:26.210 Yeah, it is giving wrong data. 149 09:28.670 --> 09:35.300 If we have done the order by in this particular scenario let me try. 150 09:35.930 --> 09:38.450 No, no it is giving wrong wrong data. 151 09:38.480 --> 09:44.210 So here you do not need to specify because we want department wise only right. 152 09:44.300 --> 09:50.560 We don't want second highest or third highest or fourth highest department wise or anything. 153 09:50.560 --> 09:54.160 So order by is not required in this particular case. 154 09:57.700 --> 10:02.680 So what is the advantage of this one over this one in the group. 155 10:02.710 --> 10:06.130 Why group by close data will be summarized right. 156 10:07.840 --> 10:17.020 We cannot see entire row data or all the rows data. 157 10:17.050 --> 10:19.240 That is the limitation of group by. 158 10:19.330 --> 10:21.910 But when we use the sum over clause. 159 10:25.090 --> 10:30.880 Then we can see row wise data as well. 160 10:30.910 --> 10:39.790 And that is the advantage advantage to do reporting right. 161 10:41.140 --> 10:45.940 If it is a requirement to do reporting based upon row wise. 162 10:48.720 --> 10:49.020 Okay. 163 10:49.020 --> 10:51.750 Now let's see one more example. 164 10:52.980 --> 10:57.210 So over here what is our requirement is salary per. 165 10:57.390 --> 11:01.050 So let me comment this one first of all. 166 11:03.570 --> 11:04.080 Okay. 167 11:04.080 --> 11:09.270 So over here what is my requirement is salary percentage with respect to total. 168 11:11.310 --> 11:14.610 Salary percentage with respect to total. 169 11:14.640 --> 11:19.770 So now let us see what is my total salary. 170 11:21.900 --> 11:29.490 So let me try salary comma or select star from EMP. 171 11:29.490 --> 11:31.860 I think we have already copied the data. 172 11:32.610 --> 11:33.210 Okay. 173 11:33.210 --> 11:34.200 We have already copied. 174 11:34.230 --> 11:36.990 Let me write it again one minute. 175 11:48.000 --> 11:52.920 So now over here, my requirement is I want salary. 176 11:52.920 --> 11:54.900 And now what is my total? 177 11:54.930 --> 11:56.580 What is my total salary? 178 11:56.580 --> 11:57.750 It is over here, right? 179 11:57.750 --> 11:59.070 It is over here. 180 11:59.550 --> 12:00.840 Sum of salary. 181 12:00.840 --> 12:05.460 So this will be my total and it will come through all the rows. 182 12:05.460 --> 12:06.090 Right. 183 12:08.160 --> 12:17.220 Now how much salary this particular employee is acquiring with respect to the total salary. 184 12:17.250 --> 12:19.560 How can I identify that one. 185 12:19.560 --> 12:22.830 So what we need to do we need to do 5000. 186 12:24.840 --> 12:29.280 Divided by eight by 37595. 187 12:29.280 --> 12:31.290 And multiply by 100. 188 12:32.760 --> 12:34.290 So 13 percentage. 189 12:34.290 --> 12:34.890 Right. 190 12:35.100 --> 12:36.060 Let me try. 191 12:36.090 --> 12:40.470 If I drag and drop it will come okay. 192 12:40.470 --> 12:41.400 It is not coming. 193 12:41.400 --> 12:44.560 Correct result Again. 194 12:44.680 --> 12:49.510 Let me try this divided by this. 195 12:50.470 --> 12:52.540 Multiply by 100. 196 12:52.570 --> 12:53.170 Right. 197 12:53.170 --> 12:56.650 So this is my actual requirement. 198 12:58.030 --> 13:00.400 Actual requirement at this time. 199 13:00.820 --> 13:01.600 How much? 200 13:01.630 --> 13:07.300 One person salary is coming with respect to the total salary. 201 13:07.330 --> 13:10.360 How much percentage I want in percentage. 202 13:10.390 --> 13:11.140 So what? 203 13:11.170 --> 13:17.710 What I have to do, I have to do, uh, salary divided by. 204 13:19.810 --> 13:22.900 Sum of salary and multiply by 100. 205 13:22.930 --> 13:23.530 Right. 206 13:23.530 --> 13:25.150 This should be the formula. 207 13:26.800 --> 13:30.670 So now let's see the SQL query for the same. 208 13:30.760 --> 13:34.360 What I have done see over here. 209 13:35.080 --> 13:40.570 Salary divided by sum of salary over why I have mentioned over here. 210 13:40.600 --> 13:50.440 Over because I want each of the employee percentage wise salary amongst total salary. 211 13:50.470 --> 13:50.770 Right. 212 13:50.800 --> 13:52.210 Each of the employee. 213 13:52.600 --> 13:56.860 So this will give me let's execute me this query. 214 13:59.860 --> 14:01.090 So see. 215 14:03.280 --> 14:06.130 Over here it is coming from 0.13. 216 14:06.160 --> 14:09.700 And over here it is coming from 13.29 right. 217 14:09.790 --> 14:11.470 13.29. 218 14:11.470 --> 14:16.300 So in this particular result set I need to do multiply by 100 right. 219 14:16.960 --> 14:18.580 Multiply by 100. 220 14:18.610 --> 14:22.240 So what I have done now forget this query. 221 14:22.270 --> 14:25.330 What I have done over here multiply by 100. 222 14:25.360 --> 14:27.550 So it will give me the correct result. 223 14:27.580 --> 14:28.240 Right. 224 14:29.380 --> 14:32.710 See whatever is there in the Excel file 2.12. 225 14:32.830 --> 14:37.330 Then 4.25, then 3.32. 226 14:38.290 --> 14:39.790 So everything is correct. 227 14:39.820 --> 14:43.150 What I have shown you over here in and formalize this one. 228 14:43.720 --> 14:51.820 And here I have used the sum over function, some aggregate ranking function over here again in this 229 14:51.820 --> 14:53.050 purpose as well. 230 14:54.280 --> 14:57.310 Right in this particular purpose as well. 231 15:02.620 --> 15:03.490 Okay. 232 15:08.350 --> 15:09.220 Okay. 233 15:10.600 --> 15:14.200 Now let's see one more thing. 234 15:16.510 --> 15:18.790 I want to do the same calculation. 235 15:18.790 --> 15:25.510 But now this time department wise I want to calculate this. 236 15:27.520 --> 15:34.180 This particular person say person salary in how much total of department wise. 237 15:34.180 --> 15:36.550 How much total of department wise. 238 15:36.550 --> 15:38.320 So over here I need to do. 239 15:38.350 --> 15:38.950 Total of. 240 15:38.950 --> 15:40.270 Department 20. 241 15:40.640 --> 15:44.000 total of department ten and total of Department 30. 242 15:44.270 --> 15:46.310 That what we have done over here, right. 243 15:46.310 --> 15:50.930 That what we have done over here in this particular result set. 244 15:54.170 --> 15:59.390 And now actually this data is not according to the department wise. 245 16:00.110 --> 16:02.420 So let me shot. 246 16:08.780 --> 16:09.830 J right. 247 16:18.830 --> 16:25.850 So now how much of salary this 2400 in that particular department only. 248 16:25.850 --> 16:32.150 So I need to do the sum of this particular. 249 16:35.660 --> 16:36.680 How I don't know. 250 16:42.830 --> 16:49.700 Okay, one minute equal to this divided by this particular amount. 251 16:49.760 --> 16:51.710 And then multiply by 100. 252 16:51.710 --> 16:52.400 Right. 253 16:53.630 --> 16:54.260 Okay. 254 16:54.260 --> 17:00.680 Now let's see whether we are getting the correct result every time or not. 255 17:01.040 --> 17:04.340 So let me execute this particular query now. 256 17:08.360 --> 17:08.990 Okay. 257 17:09.020 --> 17:15.320 See over here 20 point point 13 then 49.26 then 12.80. 258 17:15.530 --> 17:23.060 Now when the department number is changing, whether it is giving correct result or not 17.16 it is 259 17:23.060 --> 17:24.050 giving correct result. 260 17:24.080 --> 17:28.160 Now we do not need to divide by entire total. 261 17:28.160 --> 17:32.030 We need to divide it by this particular department wise total only. 262 17:32.030 --> 17:32.660 Right. 263 17:33.200 --> 17:35.870 And that is why I have written over here. 264 17:37.050 --> 17:41.910 700 multiplied by salary divided by sum of salary. 265 17:41.940 --> 17:49.980 And now I have done the partition by department also right partition by department also. 266 17:50.640 --> 17:53.280 So that will give me the correct result. 267 18:02.010 --> 18:05.730 Now let me show you one more important thing. 268 18:05.730 --> 18:06.900 Very important. 269 18:08.280 --> 18:10.830 So let me copy this data. 270 18:10.860 --> 18:12.990 Let me first execute this query. 271 18:20.190 --> 18:22.530 And show you now what we are trying to do. 272 18:22.560 --> 18:24.750 We are trying to do the running total. 273 18:25.260 --> 18:30.090 We are trying to do the running total across all the employees. 274 18:30.870 --> 18:32.220 So now. 275 18:36.390 --> 18:38.070 What I have done over here. 276 18:38.100 --> 18:39.480 Now I have done over here. 277 18:39.510 --> 18:40.530 Order by salary. 278 18:40.530 --> 18:41.460 Ascending. 279 18:42.270 --> 18:43.770 This particular column. 280 18:43.770 --> 18:44.220 Salary. 281 18:44.250 --> 18:45.180 Sum of salary. 282 18:45.210 --> 18:46.530 Right over. 283 19:00.780 --> 19:01.260 Uh, guys. 284 19:01.260 --> 19:02.160 One minute. 285 19:07.350 --> 19:11.880 So over here, what we are doing, we are doing the running total. 286 19:12.000 --> 19:13.410 Running total. 287 19:13.440 --> 19:18.000 So over here, sum of salary over order by salary ascending. 288 19:18.000 --> 19:26.370 Because I want to do one, one after one, uh, one after one employee addition. 289 19:26.370 --> 19:26.970 Right. 290 19:27.930 --> 19:30.720 Then, then what I have to do, I have to do first. 291 19:30.720 --> 19:32.160 It will be 800. 292 19:32.160 --> 19:34.650 So now 800 plus 950. 293 19:34.650 --> 19:40.140 So I over here in this particular case, I need to do order by ascending. 294 19:40.920 --> 19:42.510 Order by ascending. 295 19:44.640 --> 19:45.540 Ascending of what? 296 19:45.570 --> 19:46.710 Ascending of salary. 297 19:46.740 --> 19:47.160 Right. 298 19:47.190 --> 19:48.630 Ascending of salary. 299 19:48.750 --> 19:50.100 Now. 300 19:53.190 --> 19:58.260 Now let me show you the result set. 301 19:58.680 --> 20:02.370 And now I will unmute you for one minute. 302 20:05.970 --> 20:08.880 Can anyone tell me what is the problem over here? 303 20:08.910 --> 20:11.100 Is there any problem or not? 304 20:11.760 --> 20:12.840 Screen was not visible. 305 20:17.370 --> 20:18.720 Can you share the screen? 306 20:19.080 --> 20:19.410 Okay. 307 20:19.410 --> 20:21.270 Yeah, I'm sharing again. 308 20:26.310 --> 20:27.900 It is visible now. 309 20:27.930 --> 20:28.440 Yeah. 310 20:29.190 --> 20:29.940 Yes. 311 20:34.540 --> 20:35.050 Yeah. 312 20:35.050 --> 20:41.290 So we are saying this one, we are saying this particular query and in which we are trying to do the 313 20:41.290 --> 20:45.550 running total, running total of particular all the employees. 314 20:45.550 --> 20:48.640 And I have taken this data into the Excel file. 315 20:49.300 --> 20:51.460 This is my running total column. 316 20:54.550 --> 20:57.220 So now is there any problem over here. 317 21:06.970 --> 21:09.130 First value value will be 800. 318 21:09.160 --> 21:12.220 Then second value will be 800 plus 950. 319 21:12.250 --> 21:13.450 That will be correct. 320 21:13.510 --> 21:18.730 Now third value will be this one plus this one. 321 21:19.450 --> 21:21.280 So that is also correct. 322 21:21.310 --> 21:27.820 Now next value should be this one plus this one. 323 21:28.300 --> 21:30.160 So now see over here. 324 21:31.780 --> 21:32.890 This is. 325 21:33.400 --> 21:35.440 This is a wrong over here. 326 21:36.850 --> 21:37.810 This is wrong! 327 21:37.810 --> 21:38.350 Over here. 328 21:38.380 --> 21:40.300 Now, what is the next value? 329 21:40.540 --> 21:42.790 Next value should be this one. 330 21:42.790 --> 21:45.010 And this one. 331 21:45.190 --> 21:49.690 So now this is correct. 332 21:53.440 --> 21:54.460 This is correct. 333 21:54.460 --> 21:56.110 But above one is wrong. 334 21:56.230 --> 21:57.310 Why? 335 22:00.130 --> 22:01.630 Why it is wrong? 336 22:01.660 --> 22:07.330 Because that is the catch over here. 337 22:07.330 --> 22:09.190 That is the catch over here. 338 22:09.790 --> 22:19.960 So by default it is this query, this query, this sum of salary over order by salary, ascending, 339 22:19.960 --> 22:26.080 running total and below query below query will be the same. 340 22:29.500 --> 22:31.510 Below query will be the same. 341 22:33.130 --> 22:39.790 So why it is taking it is, uh, this portion is hidden over here. 342 22:39.970 --> 22:44.350 This portion is hidden over here. 343 22:44.350 --> 22:45.250 Range between. 344 22:45.250 --> 22:45.670 Unbound. 345 22:45.700 --> 22:46.660 Preceding. 346 22:46.660 --> 22:48.790 So what is the unbound preceding? 347 22:48.790 --> 22:49.630 Over here. 348 22:52.060 --> 22:54.400 Unbound preceding means. 349 22:54.910 --> 22:59.080 What are the rows above this particular value? 350 23:00.340 --> 23:03.550 What are the rows above this particular value. 351 23:03.880 --> 23:07.000 So all these three rows are above this particular value. 352 23:07.030 --> 23:07.390 Right. 353 23:07.390 --> 23:12.970 All these three rows are above this particular value and current row. 354 23:12.970 --> 23:15.940 Current row means this particular value. 355 23:22.300 --> 23:23.620 This particular value. 356 23:23.620 --> 23:31.940 But because of range we have written over here it will consider this one Also 1 to 5. 357 23:31.970 --> 23:33.020 Zero and 1 to 5. 358 23:33.050 --> 23:33.380 Zero. 359 23:33.410 --> 23:35.870 It will be doing the summation of this 1 to 5. 360 23:35.900 --> 23:36.470 Zero and one. 361 23:36.470 --> 23:36.770 To five. 362 23:36.800 --> 23:37.460 Zero. 363 23:37.520 --> 23:40.880 So what is the summation of 1 to 5? 364 23:40.910 --> 23:42.710 Zero plus 1 to 5. 365 23:42.740 --> 23:43.430 Zero. 366 23:43.700 --> 23:45.590 It is 2500. 367 23:45.590 --> 23:50.810 And then what it will do over here, it will do this 2500. 368 23:50.840 --> 23:54.080 This one plus this one. 369 23:54.140 --> 24:00.680 So it is showing me that result over here when we are using range between. 370 24:04.640 --> 24:07.040 When we are using range between. 371 24:07.100 --> 24:10.160 But if we are now what is the solution. 372 24:10.160 --> 24:11.810 This is a wrong result. 373 24:12.770 --> 24:19.040 When you are giving this, uh answer in the interview then. 374 24:21.200 --> 24:29.780 If there are a same value like this, if there are a same value like this in the salary column, then 375 24:29.780 --> 24:33.080 it will give you the wrong result. 376 24:33.110 --> 24:35.810 So what is the solution of this one? 377 24:36.500 --> 24:37.520 So one thing. 378 24:37.520 --> 24:41.000 Let me try to use MP number over here. 379 24:44.300 --> 24:48.470 And now let me execute this particular result. 380 24:57.140 --> 25:01.460 Order by MP number ascending. 381 25:03.380 --> 25:04.130 Okay. 382 25:06.020 --> 25:09.110 Let me try to copy this result set over here. 383 25:18.620 --> 25:22.340 And now check one minute. 384 25:26.240 --> 25:28.760 Now what is the salary of one? 385 25:28.790 --> 25:29.510 Zero. 386 25:29.540 --> 25:30.830 This is correct. 387 25:33.140 --> 25:33.530 Okay. 388 25:33.560 --> 25:35.750 We have to see the this column. 389 25:35.750 --> 25:36.350 Right. 390 25:42.500 --> 25:43.940 Running total. 391 25:44.000 --> 25:45.530 Running total. 392 25:54.830 --> 25:56.510 5800. 393 25:56.540 --> 25:58.280 This one is also wrong. 394 25:59.180 --> 26:02.600 This one is also giving us the wrong result. 395 26:03.170 --> 26:05.840 We have done order by EMP number. 396 26:05.840 --> 26:10.490 And then we are doing the sum of salary EMP number. 397 26:11.150 --> 26:13.670 Then what is the salary of this one? 398 26:14.480 --> 26:15.200 800. 399 26:15.230 --> 26:15.530 Right. 400 26:15.560 --> 26:16.580 800. 401 26:17.690 --> 26:18.890 Then what is the next. 402 26:18.920 --> 26:23.390 Next employee in ordering 101. 403 26:26.070 --> 26:26.520 Okay. 404 26:26.550 --> 26:29.460 First one is first one is 5000. 405 26:29.610 --> 26:30.180 Right? 406 26:30.210 --> 26:36.180 First one actually we need to do sorting based upon ordering sort. 407 26:36.810 --> 26:38.160 Custom sort. 408 26:38.670 --> 26:41.550 Then let me sort it with k. 409 26:43.920 --> 26:44.490 Okay. 410 26:44.490 --> 26:49.110 Now see 5000 5800. 411 26:49.140 --> 26:52.950 Now see 5000 5000. 412 26:52.980 --> 26:55.020 Then 5800. 413 26:55.590 --> 26:57.300 Right now it is giving. 414 26:58.260 --> 27:00.720 Then 5800 plus. 415 27:00.720 --> 27:05.040 We need to do plus this 1674 four. 416 27:05.070 --> 27:05.400 Right? 417 27:05.400 --> 27:06.480 It is correct. 418 27:07.680 --> 27:14.250 Now over here we need to do 74 plus this one. 419 27:15.090 --> 27:17.610 So this time also it is correct. 420 27:17.970 --> 27:26.160 But now no salary or no no salary or no employee are now getting duplicated, right? 421 27:26.490 --> 27:30.300 No salary or no employee are now getting duplicated. 422 27:31.020 --> 27:32.310 That is why this is. 423 27:32.340 --> 27:32.670 Sorry. 424 27:32.700 --> 27:35.670 No salary and no employee number, right? 425 27:37.050 --> 27:38.910 So at this time it is giving. 426 27:38.910 --> 27:39.270 Correct. 427 27:39.270 --> 27:40.770 But now one more solution. 428 27:40.770 --> 27:44.430 Is there, one more solution is there? 429 27:44.430 --> 27:46.140 And what is that solution? 430 27:46.410 --> 27:52.440 Let me try to use over here rows between over here. 431 27:52.440 --> 27:58.110 Let me copy this entire thing and. 432 28:03.720 --> 28:05.730 Give me some other column name. 433 28:05.730 --> 28:06.780 And now. 434 28:10.620 --> 28:16.260 Rows between and now run this whatever previously it is salary. 435 28:16.260 --> 28:16.830 Right. 436 28:17.970 --> 28:19.680 Now run this query. 437 28:23.200 --> 28:24.910 Now run this query. 438 28:26.500 --> 28:27.910 Now see? 439 28:33.820 --> 28:37.720 Now see whether results are getting correct or not. 440 28:46.900 --> 28:49.480 So now we have to see this particular column. 441 28:49.480 --> 28:49.750 Right. 442 28:49.750 --> 28:54.340 Because this column is already giving us the wrong result. 443 28:55.000 --> 28:57.730 This column is already giving us the wrong result. 444 28:57.730 --> 29:02.200 So let's try to see with this particular column. 445 29:02.230 --> 29:04.060 Let's try with this one. 446 29:04.090 --> 29:08.410 Firstly what we have to do this one and this one right. 447 29:08.410 --> 29:13.030 So first it will be 800. 448 29:13.030 --> 29:21.880 And now over here we need to do some of this particular column and this column right. 449 29:23.890 --> 29:26.020 What is happening over here? 450 29:26.050 --> 29:28.660 Okay, we have done some mistake. 451 29:28.960 --> 29:31.900 This one plus this one. 452 29:32.020 --> 29:34.000 Now 1750. 453 29:34.030 --> 29:34.570 It is correct. 454 29:34.570 --> 29:34.870 Result. 455 29:34.900 --> 29:36.550 Right 1750. 456 29:36.670 --> 29:37.570 Now again. 457 29:37.570 --> 29:38.890 This 1750. 458 29:38.920 --> 29:43.480 I need to do addition of this 1100. 459 29:43.480 --> 29:45.730 So again it is giving correct result. 460 29:45.760 --> 29:50.530 Now this column we need to do some of these value. 461 29:50.590 --> 29:53.440 Now see over here for 100. 462 29:53.830 --> 29:55.690 This is our requirement. 463 29:55.960 --> 29:57.670 This is the correct result. 464 29:57.700 --> 30:03.670 Now let me do again this one for 100 plus this one. 465 30:04.510 --> 30:05.800 So 5350. 466 30:05.830 --> 30:07.570 Again this is the correct result. 467 30:07.600 --> 30:13.870 Now if I do again this 53505350. 468 30:19.060 --> 30:23.800 Plus this 1300, so it is giving me correct results this time. 469 30:23.800 --> 30:25.390 This time it is giving me. 470 30:25.510 --> 30:27.040 Actual correct result. 471 30:27.070 --> 30:27.580 Why? 472 30:27.610 --> 30:33.760 Because this time we have used the instead of rows between. 473 30:34.420 --> 30:36.850 By default it is also rows between. 474 30:36.850 --> 30:37.210 Right? 475 30:37.240 --> 30:38.020 Right. 476 30:38.050 --> 30:38.650 Sorry. 477 30:38.650 --> 30:41.110 By default also it is range between. 478 30:41.380 --> 30:43.810 And this time we have done rows between. 479 30:43.810 --> 30:47.140 So now for every row it will consider. 480 30:48.070 --> 30:50.200 It will consider for every row. 481 30:50.230 --> 30:53.800 So once it is reaching over here or over here. 482 30:53.830 --> 30:56.740 It will consider this row only. 483 30:56.740 --> 30:58.870 It will not consider this range. 484 30:59.380 --> 31:01.690 It will consider this particular rows. 485 31:01.720 --> 31:08.290 Only in this row addition it will do to this particular row. 486 31:08.620 --> 31:09.490 Right. 487 31:09.520 --> 31:11.080 It will not consider this. 488 31:11.110 --> 31:12.250 Entire range. 489 31:12.550 --> 31:16.210 And that is why it is giving me the correct result over here. 490 31:20.690 --> 31:24.260 That is why it is giving me the correct result over here, right? 491 31:28.580 --> 31:32.360 Now you can speak if you have any doubt. 492 31:42.740 --> 31:43.730 Okay. 493 31:46.370 --> 31:47.450 We will see everything. 494 31:47.450 --> 31:48.110 This unbound. 495 31:48.140 --> 31:48.590 Preceding. 496 31:48.620 --> 31:49.040 Unbound. 497 31:49.040 --> 31:49.430 Following. 498 31:49.460 --> 31:50.240 Unbound. 499 31:51.470 --> 31:52.640 Preceding. 500 31:53.270 --> 31:53.930 Current. 501 31:53.930 --> 31:54.290 Row. 502 31:54.320 --> 31:55.100 Let's see. 503 31:55.130 --> 31:57.500 Let's see now few more examples. 504 31:58.400 --> 32:00.800 Let's see now few more examples. 505 32:06.290 --> 32:07.700 So now what. 506 32:07.700 --> 32:10.430 It will let me first copy the result set. 507 32:10.880 --> 32:12.590 Then I will explain. 508 32:12.800 --> 32:13.580 Okay. 509 32:13.610 --> 32:16.340 Over here multiple queries are there okay. 510 32:22.730 --> 32:26.240 Let me first copy this result set over here. 511 32:26.240 --> 32:29.390 I have done the partition by right, right over here. 512 32:29.390 --> 32:31.610 I have done the partition by by. 513 32:31.940 --> 32:33.050 So. 514 32:40.250 --> 32:41.030 Okay. 515 32:41.120 --> 32:42.380 So now. 516 32:49.730 --> 32:51.920 Running total within the department. 517 32:51.920 --> 32:53.090 Running total. 518 32:53.120 --> 32:58.490 Now we want to consider running total within the department. 519 32:58.490 --> 32:58.850 Right. 520 32:58.880 --> 32:59.570 Right. 521 32:59.570 --> 33:06.320 So what we have to do see over here firstly it will consider the department number ten only. 522 33:06.350 --> 33:06.830 Right. 523 33:06.830 --> 33:10.220 So for in the first row it will be 1300. 524 33:10.250 --> 33:16.520 Now second row 1300 plus this particular 14 1400. 525 33:16.850 --> 33:18.050 Right then. 526 33:18.080 --> 33:18.530 Third row. 527 33:18.560 --> 33:19.340 What it will do. 528 33:19.370 --> 33:22.430 This one plus this one. 529 33:22.730 --> 33:23.180 Right. 530 33:23.180 --> 33:23.960 So it is showing. 531 33:23.990 --> 33:24.410 Correct. 532 33:24.410 --> 33:27.410 Only now again for this one. 533 33:27.440 --> 33:31.730 It is giving this one plus this one. 534 33:33.110 --> 33:34.910 So it is correct only. 535 33:34.910 --> 33:40.490 But now whenever the department is changing it will be starting from scratch again. 536 33:41.150 --> 33:43.070 It will be starting from scratch again. 537 33:43.070 --> 33:47.000 So what will be the current total over here? 538 33:47.030 --> 33:49.250 This time it will be 800, right? 539 33:50.480 --> 33:55.070 Then 800 plus 1100. 540 33:55.100 --> 33:57.350 So it will be 1900. 541 33:57.380 --> 34:06.500 Then what it will do over here next time 19001900 plus this 1600. 542 34:06.530 --> 34:10.670 So it will be 3500 right. 543 34:10.700 --> 34:13.130 It will be 3500 right. 544 34:13.250 --> 34:16.740 So it will be giving me the running total department wise. 545 34:17.190 --> 34:20.820 And for that purpose I have done this one. 546 34:22.980 --> 34:25.200 Partitioned by department. 547 34:27.330 --> 34:38.340 Order by salary rose between again whenever we want the result set in a pure correct manner. 548 34:38.520 --> 34:43.170 Instead of using range between, always use rose between. 549 34:44.130 --> 34:50.670 Always use rose between because it will consider each of the row right each of the row. 550 34:53.040 --> 34:53.940 Now what? 551 34:53.970 --> 34:55.800 What I have done unbound preceding. 552 34:55.800 --> 34:57.990 So let me explain you. 553 35:01.710 --> 35:06.570 That one also, if I have missed that one, what it will do over here. 554 35:06.600 --> 35:07.980 Now see over here. 555 35:14.130 --> 35:21.390 In this particular case preceding what it will check. 556 35:21.630 --> 35:27.270 It will check the rows between unbound preceding. 557 35:27.270 --> 35:31.200 So what is the unbound preceding for this particular row? 558 35:31.830 --> 35:35.130 What is the unbound preceding for this particular row? 559 35:36.150 --> 35:44.190 This one, because of we are considering department wise right at that time, we have to check department 560 35:44.220 --> 35:49.350 wise unbound preceding only department wise unbound preceding only. 561 35:49.350 --> 35:56.490 So all this row in this particular department will be the unbound preceding right, unbound preceding 562 35:56.490 --> 35:57.120 right. 563 35:57.690 --> 35:59.310 And what is the current row? 564 36:00.060 --> 36:01.800 Current row will be the same. 565 36:01.800 --> 36:02.370 This one. 566 36:02.370 --> 36:03.990 Right 2975. 567 36:04.260 --> 36:13.800 So what it will do, it will do some of this one plus some of this one plus some of this one plus some 568 36:13.800 --> 36:26.220 of these 16475 and if we do this one plus this one, then also it will give me the same thing, right? 569 36:27.990 --> 36:29.010 Current row. 570 36:29.040 --> 36:36.270 Now when we move to the next row, what is the unbound preceding unbound preceding of all the row which 571 36:36.270 --> 36:37.290 is above this one? 572 36:37.290 --> 36:38.250 All the row. 573 36:40.320 --> 36:41.640 And what is my current row? 574 36:41.670 --> 36:42.150 This time? 575 36:42.150 --> 36:44.370 My current row will be 3000. 576 36:44.430 --> 36:46.950 So what is the sum of all this? 577 36:47.310 --> 36:48.750 9475. 578 36:48.780 --> 36:49.200 Right. 579 36:49.200 --> 36:50.550 So let me do that. 580 36:50.550 --> 36:55.440 6475 plus 3009 475 right. 581 36:57.450 --> 37:01.950 So it is this is the meaning of unbound preceding and current row. 582 37:02.160 --> 37:05.430 Now see what what we have written over here. 583 37:05.640 --> 37:11.610 We have seen already this this right order by salary unbound preceding and current row. 584 37:11.800 --> 37:16.960 Let me execute this data, this query. 585 37:29.110 --> 37:31.840 Now I think second row is giving the same. 586 37:36.580 --> 37:37.660 800. 587 37:43.360 --> 37:43.810 Okay. 588 37:43.840 --> 37:47.560 This one is giving the running total as of now right. 589 37:47.590 --> 37:48.490 Running total. 590 37:48.520 --> 37:53.380 But what is this one that we have to understand now? 591 37:53.380 --> 37:54.790 What is written over here? 592 37:54.820 --> 37:57.640 Order by salary rows between. 593 37:57.820 --> 38:02.410 Now see over here I have written unbound, preceding and current row. 594 38:02.440 --> 38:02.950 Now what? 595 38:02.950 --> 38:05.860 I have written current row and unbound. 596 38:05.890 --> 38:10.720 Following current row and unbound following. 597 38:10.750 --> 38:12.610 Now what is this current row? 598 38:13.120 --> 38:18.400 So in this particular case current row will be the same current row right. 599 38:19.060 --> 38:20.620 Same current row. 600 38:20.740 --> 38:22.300 And unbound following. 601 38:22.300 --> 38:23.830 Preceding means what? 602 38:24.070 --> 38:25.510 What is the above? 603 38:25.540 --> 38:27.070 Above values. 604 38:27.130 --> 38:33.160 All the above values are preceding and all the below values will be following. 605 38:34.870 --> 38:37.750 And when we are mentioning unbound one. 606 38:37.750 --> 38:41.590 When we are mentioning unbound preceding, then all the rows above. 607 38:41.620 --> 38:42.910 Above this one. 608 38:43.750 --> 38:45.460 Is there any row above this one? 609 38:45.460 --> 38:45.730 No. 610 38:45.730 --> 38:46.390 Right. 611 38:47.200 --> 38:48.130 No. 612 38:48.130 --> 38:49.930 So we have mentioned over here. 613 38:49.960 --> 38:50.800 Current row anyhow. 614 38:50.830 --> 38:51.100 Right. 615 38:51.100 --> 38:53.170 So current row and unbound following. 616 38:53.170 --> 39:00.280 So what are the unbound following this for this particular current row all the rows all the rows are 617 39:00.280 --> 39:01.390 unbound following. 618 39:01.390 --> 39:06.970 So that is why it is giving me (375) 953-7595. 619 39:09.770 --> 39:11.540 37595. 620 39:11.570 --> 39:12.350 Now why? 621 39:12.350 --> 39:15.680 Why it is giving me 3736795. 622 39:15.710 --> 39:24.470 Because in this particular current row, what are the unbound following this all this record, we need 623 39:24.470 --> 39:26.930 to do some what is written over here. 624 39:26.930 --> 39:28.880 36795. 625 39:28.910 --> 39:29.450 Right. 626 39:30.200 --> 39:31.850 36795. 627 39:31.880 --> 39:35.240 So that is why 3679595 is coming. 628 39:35.270 --> 39:37.430 Why 800 is not coming? 629 39:37.430 --> 39:42.170 Because we haven't mentioned anything regarding the preceding. 630 39:43.100 --> 39:46.400 We have not mentioned anything regarding the preceding. 631 39:47.060 --> 39:50.090 We have mentioned current row and unbound following only. 632 39:50.120 --> 39:50.930 Right. 633 39:57.860 --> 40:00.710 Current row and unbound following only right. 634 40:04.850 --> 40:14.390 Now let's see We have previously seen the celery some celery running total. 635 40:15.680 --> 40:16.490 Where we have. 636 40:26.570 --> 40:26.810 To. 637 40:38.120 --> 40:38.510 Okay. 638 40:38.510 --> 40:40.250 These are my running total. 639 40:40.460 --> 40:40.850 Okay. 640 40:40.880 --> 40:41.810 One minute. 641 40:53.270 --> 40:54.680 Now see. 642 40:56.660 --> 40:58.490 Now see this one. 643 40:59.780 --> 41:02.090 So now compare this result set. 644 41:02.090 --> 41:03.590 And this result set. 645 41:04.580 --> 41:05.720 Both are same. 646 41:05.750 --> 41:06.950 See over here. 647 41:07.550 --> 41:09.650 No, I think in this it is wrong. 648 41:09.650 --> 41:10.220 One. 649 41:11.510 --> 41:12.710 One minute. 650 41:12.770 --> 41:13.550 Over here. 651 41:13.550 --> 41:14.840 We need to mention. 652 41:19.280 --> 41:21.290 Between unbound preceding. 653 41:21.620 --> 41:25.370 Unbound and. 654 41:28.970 --> 41:29.720 Current row. 655 41:29.720 --> 41:29.960 Okay. 656 41:29.990 --> 41:31.640 That is the same one only. 657 41:34.670 --> 41:35.120 Unbound. 658 41:35.120 --> 41:36.380 Preceding and current row. 659 41:38.420 --> 41:42.500 So we have we have seen this one right over here unbound preceding. 660 41:42.500 --> 41:48.560 So let me show you this one for this particular row. 661 41:48.590 --> 41:50.270 What are the unbound preceding. 662 41:50.300 --> 41:53.300 Unbound preceding in the end current row. 663 41:53.300 --> 41:59.330 So this row and this row are the are the current row and unbound preceding is this row right. 664 41:59.420 --> 42:03.290 So that is why it is showing me over here 1750. 665 42:03.590 --> 42:09.750 Now for this particular row, what are the unbound preceding all these two rows are the unbound preceding, 666 42:09.750 --> 42:12.030 and this current row is 1100. 667 42:12.240 --> 42:15.690 So all these three rows summation will be showing over here. 668 42:15.720 --> 42:17.190 32850. 669 42:18.600 --> 42:19.170 Right. 670 42:19.170 --> 42:23.130 So that is why we have written over here unbound preceding and current row. 671 42:24.600 --> 42:28.260 Now up to here anyone has any doubt they can ask me. 672 42:31.290 --> 42:33.030 Or we will take I think. 673 42:33.030 --> 42:34.620 No nothing is further. 674 42:34.650 --> 42:35.280 Okay. 675 42:35.310 --> 42:38.100 Just let me take this one example only. 676 42:39.180 --> 42:40.500 I think one. 677 42:40.650 --> 42:41.010 Okay. 678 42:41.040 --> 42:42.870 These two example are pending. 679 42:44.610 --> 42:46.950 Can I take now or tomorrow. 680 42:49.830 --> 42:52.080 These two examples are left. 681 42:54.900 --> 42:55.140 Okay. 682 42:55.230 --> 42:59.550 If you have a work then we can take it tomorrow. 683 42:59.550 --> 43:00.690 No problem.