1 00:01.700 --> 00:01.910 Okay. 2 00:01.910 --> 00:08.870 So now we will see the spatial functions available in SQL server or any of the RDBMS. 3 00:08.870 --> 00:13.220 So let's continue with the spatial functions. 4 00:14.840 --> 00:19.970 So let's say we have a table this one select star from customer. 5 00:20.660 --> 00:28.430 So over here if you see first name middle name and last name they are given as right first name, middle 6 00:28.460 --> 00:29.630 name and last name. 7 00:29.720 --> 00:35.990 But in some of the uh rows middle name is not provided. 8 00:36.020 --> 00:37.910 Middle middle middle name is null. 9 00:38.510 --> 00:46.640 So over here what will happen when we are connecting first name, middle name and last name with plus 10 00:46.670 --> 00:47.540 operator? 11 00:48.350 --> 00:51.860 What it will give result it will give. 12 00:51.890 --> 00:55.340 Let's see the result. 13 00:56.300 --> 01:00.270 So see over here in the full name we are connecting. 14 01:00.300 --> 01:03.060 First name, middle name and last name as full name. 15 01:03.780 --> 01:05.220 And over here in the middle. 16 01:05.250 --> 01:05.490 Name. 17 01:05.520 --> 01:07.980 Three records are having null values. 18 01:08.430 --> 01:16.470 So even though the first name and last name has value in the full name, it is showing as null. 19 01:17.820 --> 01:21.060 It is not showing like first name and last name. 20 01:22.290 --> 01:24.690 So what is the problem over here? 21 01:26.250 --> 01:27.000 So? 22 01:28.590 --> 01:31.740 So it is written over here. 23 01:31.770 --> 01:40.170 Whenever we perform some arithmetic operation with null or do concatenation operation with null, it 24 01:40.170 --> 01:44.430 means this arithmetic means we are doing concatenation right over here. 25 01:45.120 --> 01:47.760 So with null then it returns null. 26 01:48.600 --> 01:49.920 Ideally it is wrong right? 27 01:49.950 --> 01:51.000 Ideally it is wrong. 28 01:51.000 --> 01:55.410 Ideally it should give first name and last name in this case. 29 01:55.920 --> 02:00.790 But because of in the middle name it is null for full name. 30 02:00.790 --> 02:02.740 It is providing over here null. 31 02:02.920 --> 02:07.210 Now again see this time from EMP. 32 02:07.570 --> 02:12.340 Let's see select star from EMP. 33 02:13.720 --> 02:15.400 And now let's see. 34 02:16.810 --> 02:19.840 Now if you see over here salary and commission. 35 02:19.840 --> 02:26.050 So in in the commission column there are many columns here has null values right. 36 02:26.080 --> 02:27.880 Many column has null value. 37 02:28.000 --> 02:37.990 So when we are doing salary plus commission what will happen when we are doing salary plus commission. 38 02:38.830 --> 02:41.680 Whenever in the commission column it is null. 39 02:42.670 --> 02:44.800 In the total it is also showing null. 40 02:44.830 --> 02:47.890 Ideally it should be 34220. 41 02:47.920 --> 02:51.850 For this one it should be 2450 for this one. 42 02:51.850 --> 02:56.590 But because of null value is over here it is showing null. 43 02:56.980 --> 02:58.350 Over here It is showing. 44 02:58.350 --> 02:58.650 Correct? 45 02:58.680 --> 02:59.280 Right. 46 02:59.370 --> 03:04.950 1 to 5 zero and 14002650. 47 03:04.950 --> 03:05.520 Right. 48 03:05.910 --> 03:08.700 But whenever null, it is showing null over here. 49 03:08.700 --> 03:10.380 So this is also wrong. 50 03:11.010 --> 03:14.010 Now what is the solution for this one. 51 03:14.370 --> 03:16.830 So there are multiple solutions are there. 52 03:16.860 --> 03:18.690 One solution is concrete. 53 03:20.070 --> 03:27.720 In in whenever there is a string or string data type columns we have we can use the concat. 54 03:27.750 --> 03:32.220 So when we are using the concrete concat will ignore. 55 03:32.250 --> 03:36.570 Concat will ignore the null values and concrete the data. 56 03:36.600 --> 03:38.910 So see over here the result set. 57 03:38.940 --> 03:40.410 Let me okay. 58 03:42.090 --> 03:47.010 So even though null is there it will giving us what. 59 03:49.290 --> 03:51.330 Turn may may right. 60 03:51.570 --> 04:01.300 Saint John Eliot it will simply ignoring this null value Whenever there is a string data type, right? 61 04:03.340 --> 04:04.360 But now. 62 04:06.940 --> 04:09.760 Now let us see the functionality of Isnull. 63 04:09.760 --> 04:14.500 So this is known as a special functions or operators. 64 04:14.500 --> 04:16.600 Whatever you can say space is null. 65 04:17.020 --> 04:18.790 So what will Isnull will do? 66 04:19.150 --> 04:28.120 Used to deal with null values is null, will help to use to deal with null values and is null. 67 04:28.120 --> 04:31.840 Will have only two arguments argument one and argument two. 68 04:32.620 --> 04:37.570 So arguments one should be the only which may have null values. 69 04:42.070 --> 04:43.600 Arguments two. 70 04:43.630 --> 04:47.920 Argument two should be the one which you want if argument one is null. 71 04:47.920 --> 04:49.990 So what will happen over here? 72 04:54.490 --> 05:00.410 Is null So in place of when when this argument argument is null, right? 73 05:01.010 --> 05:04.670 Then it will return argument two value. 74 05:04.700 --> 05:06.380 If it is not null. 75 05:07.850 --> 05:09.500 If it is not null. 76 05:09.590 --> 05:12.140 And what will happen is null. 77 05:17.420 --> 05:20.030 Now if argument one is not null, right? 78 05:20.030 --> 05:20.360 When? 79 05:20.390 --> 05:25.970 Argument one is not null, it will return argument one value only. 80 05:33.410 --> 05:38.420 And over here argument two will return. 81 05:41.930 --> 05:54.750 When argument one value is null and argument two value is not null Right. 82 05:54.840 --> 05:57.450 So this is the use of isnull. 83 05:57.660 --> 05:59.880 Now let's see a few example. 84 06:00.120 --> 06:05.220 So what will return in this particular case. 85 06:05.370 --> 06:06.810 What will return. 86 06:06.810 --> 06:10.740 So it will return me 400 right. 87 06:10.740 --> 06:12.300 It will return me 400. 88 06:12.300 --> 06:13.590 So let's see. 89 06:13.770 --> 06:17.640 Because argument one is not null right now. 90 06:17.640 --> 06:19.800 In this particular case what will return. 91 06:19.800 --> 06:24.930 It will return 500 because argument one is null right. 92 06:27.510 --> 06:28.620 Now let's see. 93 06:28.650 --> 06:30.630 Now let's see this example. 94 06:34.470 --> 06:43.500 Which we have seen over here above right where a full name is giving null whenever middle name is null. 95 06:43.680 --> 06:47.430 So over here it will not happen. 96 06:47.430 --> 06:48.300 Let us see. 97 06:51.630 --> 06:54.760 So over here it even the first name. 98 06:54.760 --> 06:56.320 So it will give first name. 99 06:56.380 --> 06:57.370 Then over here. 100 06:57.400 --> 06:59.320 What this one will return. 101 06:59.620 --> 07:01.600 So this one will return. 102 07:02.860 --> 07:04.180 Let's see this one. 103 07:05.410 --> 07:08.260 Select this from. 104 07:14.830 --> 07:18.040 It will return me simply like this. 105 07:21.460 --> 07:22.360 Nothing, right? 106 07:22.390 --> 07:23.260 Nothing. 107 07:23.590 --> 07:26.620 So over here it will able to concat. 108 07:26.650 --> 07:29.830 Now if I mention over here last name. 109 07:44.170 --> 07:47.950 So all the values as last name available with me, right? 110 07:47.980 --> 07:50.530 And first name also all the values are there. 111 07:50.560 --> 07:59.360 So it will simply do plus with this empty value and return everything as it is in the full name. 112 07:59.450 --> 08:00.680 So concrete. 113 08:00.830 --> 08:06.530 With concrete, we have able to retrieve the full name without concrete. 114 08:06.530 --> 08:08.930 Also, we will able to retrieve the full name. 115 08:09.140 --> 08:11.450 Now see over here in the commission. 116 08:13.910 --> 08:15.170 So what? 117 08:15.170 --> 08:17.210 We have written me this one. 118 08:17.210 --> 08:20.960 So select this from EMP. 119 08:24.410 --> 08:29.060 So whenever commission is null it will be giving me like 0.00 or 0. 120 08:30.470 --> 08:37.400 Whenever commission is null, it will be giving me either 0.00 or 0 because of maybe this column is 121 08:37.400 --> 08:40.940 decimal or float value or something else. 122 08:40.940 --> 08:44.600 That is why it is giving me 0.00, right? 123 08:44.870 --> 08:54.760 Now again, if I mention salary plus commission, I want to do salary then commission, then over here. 124 08:55.750 --> 08:58.300 If I do simply like this. 125 09:06.700 --> 09:10.030 It will be returning me null values, right? 126 09:10.030 --> 09:11.860 Whenever commission is null. 127 09:13.570 --> 09:16.570 Whenever commission is null, it is giving me null values. 128 09:16.570 --> 09:23.740 But over here it is giving me proper data, right 800, then 802.50. 129 09:23.740 --> 09:27.190 Even though this is null, it is giving me 2450. 130 09:27.190 --> 09:32.800 And whenever whenever commission is there, it will adding this two value and doing the sum over here. 131 09:32.800 --> 09:33.400 Right? 132 09:34.000 --> 09:39.700 So even though null is there by writing the is null operator, it is giving. 133 09:40.480 --> 09:44.650 But over here you cannot pass in a string value, right? 134 09:44.650 --> 09:45.970 Let me try like a. 135 09:48.910 --> 09:50.660 It will give me error only, right? 136 09:50.690 --> 09:52.010 It should give me error. 137 09:52.040 --> 09:52.340 Error. 138 09:52.370 --> 09:54.680 Converting data type varchar to numeric. 139 09:56.480 --> 10:04.190 But when I am passing zero over here in the single quote, then I think it should give me result. 140 10:04.220 --> 10:04.850 Why? 141 10:05.030 --> 10:14.030 Because this one it is automatically converted to either integer or float, whatever it is the data 142 10:14.060 --> 10:14.570 type. 143 10:18.290 --> 10:21.110 So that it is giving me the value right? 144 10:21.530 --> 10:25.580 Automatically this string it is converted to like this only. 145 10:29.600 --> 10:31.370 Now let's move ahead. 146 10:32.990 --> 10:35.480 Now is null function at a time. 147 10:35.510 --> 10:45.050 Take only two arguments, but we can use nested isnull to take multiple arguments like I want to take. 148 10:45.290 --> 10:50.790 What is the functionality of Isnull Isnull will take only two arguments. 149 10:50.820 --> 10:51.990 Argument one. 150 10:52.230 --> 10:53.610 Argument two. 151 10:53.730 --> 10:59.610 But if I want to use argument again, one more argument, then what I have to do. 152 11:02.340 --> 11:06.030 Then I can do like use like nested if nested is null. 153 11:07.080 --> 11:10.860 Now this entire thing will become my first argument. 154 11:12.300 --> 11:15.120 This entire thing will become my first argument. 155 11:15.120 --> 11:17.130 And I will put comma over here. 156 11:17.130 --> 11:19.740 So this will become my first argument. 157 11:19.740 --> 11:20.700 And. 158 11:23.580 --> 11:26.580 This will become my second argument. 159 11:26.790 --> 11:34.350 So what we can do this one as as it is known as nested is null nested right. 160 11:34.380 --> 11:37.650 We have write multiple times is null inside is null. 161 11:38.190 --> 11:41.040 So that is why it is known as nested is null. 162 11:41.610 --> 11:43.980 Now what is the alternative of this one? 163 11:43.980 --> 11:48.090 Also there is a alternative of nested is null. 164 11:56.950 --> 11:59.380 In SQL is. 165 12:06.100 --> 12:08.950 Called as function because. 166 12:08.950 --> 12:11.290 What is the difference between Isnull and college? 167 12:11.890 --> 12:12.520 College. 168 12:12.550 --> 12:19.360 In the college function, you can pass multiple arguments 123456789 ten. 169 12:19.600 --> 12:20.920 There is no limit. 170 12:20.950 --> 12:25.930 Mostly there is no limit but college function what it will do. 171 12:25.990 --> 12:30.640 If this argument is null, then it will return argument two. 172 12:30.910 --> 12:36.220 If this argument in this argument is null, then it will return argument three. 173 12:36.250 --> 12:42.970 If this argument, this argument and this argument is null, then it will return argument for the college 174 12:42.970 --> 12:44.380 function will return the 175 12:50.900 --> 13:00.080 Function will return the first non null value from the given. 176 13:07.580 --> 13:09.020 First non-null value. 177 13:09.050 --> 13:12.290 So if all these three argument is null, then it will return me. 178 13:12.320 --> 13:13.400 Argument four. 179 13:14.600 --> 13:15.320 Right. 180 13:18.860 --> 13:20.510 Now let's see. 181 13:22.100 --> 13:26.330 Again this is the same problem we can use with the college also. 182 13:26.600 --> 13:27.710 So what. 183 13:28.190 --> 13:29.360 This time it will do. 184 13:29.390 --> 13:31.250 It will give me the same result. 185 13:31.280 --> 13:31.910 Right? 186 13:33.800 --> 13:35.810 It will give me the same result. 187 13:35.900 --> 13:43.580 Now if I do college with a middle name and college with last name, I think over here in the last name 188 13:43.580 --> 13:44.900 it is not required. 189 13:45.230 --> 13:46.920 Let me remove it. 190 13:50.070 --> 13:52.770 And now let me execute this thing. 191 13:54.570 --> 13:57.390 So it is giving me the same output, right? 192 13:57.420 --> 14:04.890 In the full name because of it has changed from this thing to empty string. 193 14:04.890 --> 14:09.720 And then it is concatenate all these three values with plus operator. 194 14:13.350 --> 14:18.960 Again, if you see over here now in the commission column, right. 195 14:18.990 --> 14:23.040 If the commission is null, then it will give me zero. 196 14:24.570 --> 14:27.720 If the commission is null, then it will give me zero. 197 14:27.900 --> 14:32.910 And whenever commission are null, it will add salary plus zero. 198 14:33.210 --> 14:35.460 That is our requirement, right? 199 14:36.870 --> 14:41.130 So whenever the this is null, it is giving me zero. 200 14:41.130 --> 14:45.680 And it will add 34 200 plus zero. 201 14:45.710 --> 14:47.360 It will give me the same result. 202 14:47.390 --> 14:47.930 Right? 203 14:50.300 --> 14:55.970 So we have used is we can use Isnull function, nested Isnull function, then coalesce function. 204 14:56.150 --> 14:58.370 It is depends on our requirement. 205 14:58.370 --> 15:04.940 If we have 456 parameters then better not to use nested isnull. 206 15:05.510 --> 15:09.500 And in place of nested isnull we should use college function. 207 15:15.140 --> 15:16.970 Now let's move ahead. 208 15:19.310 --> 15:28.520 Now there is one more special function is there that is known as a null if function null if now not 209 15:28.550 --> 15:30.560 null if will return. 210 15:37.280 --> 15:43.910 If argument one end is same. 211 15:46.140 --> 15:46.830 An. 212 15:57.180 --> 16:03.870 Argument one if it is not same. 213 16:03.900 --> 16:07.020 So now let's see further example. 214 16:08.040 --> 16:11.550 So over here if you see over here null. 215 16:11.580 --> 16:13.410 If null. 216 16:13.410 --> 16:17.610 If 400 comma 400 both the argument are same right. 217 16:18.090 --> 16:20.040 Both the argument are same. 218 16:20.040 --> 16:21.420 Parameter one is same. 219 16:21.450 --> 16:22.980 Parameter two is same. 220 16:23.100 --> 16:23.970 So what. 221 16:23.970 --> 16:24.750 It will return me. 222 16:24.780 --> 16:26.160 It will return me null. 223 16:29.790 --> 16:30.330 Over here. 224 16:30.330 --> 16:33.150 I don't think you can pass three argument. 225 16:33.180 --> 16:34.230 Let me try. 226 16:39.270 --> 16:40.500 Incorrect syntax. 227 16:40.500 --> 16:40.980 Right. 228 16:40.980 --> 16:46.270 You Can not pass three parameters. 229 16:46.990 --> 16:49.870 You have to pass only two parameters, right? 230 16:50.800 --> 16:54.040 In Coalesce function you can pass as much as parameters. 231 16:54.670 --> 16:56.860 But over here you can pass two parameters. 232 16:56.890 --> 16:58.450 Now see over here. 233 16:59.530 --> 17:01.990 Now it should return 300. 234 17:02.020 --> 17:08.620 Whenever both the arguments are not same, it is giving me the first argument in the result set. 235 17:11.710 --> 17:12.490 Right. 236 17:18.550 --> 17:21.430 Now there is one beautiful scenario. 237 17:22.930 --> 17:25.210 Now there is one beautiful scenario. 238 17:25.210 --> 17:30.250 Whenever we are doing five divided by zero, it will give me some error. 239 17:36.040 --> 17:38.410 We cannot ideally divide by zero, right? 240 17:38.410 --> 17:42.560 We cannot do ideally divide by zero in SQL. 241 17:42.740 --> 17:45.290 Now what is the solution of this one? 242 17:47.720 --> 17:50.000 What is the solution of this one? 243 17:50.030 --> 17:58.130 So see over here the five divided by null what it will give me. 244 17:59.330 --> 18:00.980 It will give me null. 245 18:01.880 --> 18:02.900 It will give me null. 246 18:02.900 --> 18:03.470 Right. 247 18:03.470 --> 18:05.930 So now how can I get this null? 248 18:05.960 --> 18:07.400 What should I do? 249 18:09.290 --> 18:11.330 Null if zero. 250 18:11.360 --> 18:12.650 Comma zero. 251 18:14.840 --> 18:15.980 Now see? 252 18:18.770 --> 18:21.860 It is not giving me error, right? 253 18:21.860 --> 18:24.260 In the first case it is giving me error. 254 18:24.500 --> 18:27.260 But in the second case it is not giving me error. 255 18:27.290 --> 18:28.010 Why? 256 18:29.510 --> 18:32.930 Because five divided by null should be null only in. 257 18:33.110 --> 18:37.370 We cannot specify ideally null over here. 258 18:39.230 --> 18:40.670 By default Right. 259 18:40.680 --> 18:48.540 So so to give this null as a result set, we have used this function null. 260 18:48.570 --> 18:59.610 If so, SQL will not throw the error in this kind of scenario, even though there is very rarely requirement 261 18:59.610 --> 19:01.080 of this kind of thing. 262 19:01.350 --> 19:03.690 But there should not be error, right? 263 19:05.040 --> 19:08.010 Because this one give me the null value. 264 19:08.040 --> 19:08.280 Right. 265 19:08.280 --> 19:12.390 Whenever two parameters are null, there are two parameters are same. 266 19:12.390 --> 19:16.320 It will give me null value and something divided by null. 267 19:18.300 --> 19:20.760 It will give me null only right. 268 19:20.760 --> 19:21.840 So we'll return. 269 19:21.840 --> 19:29.070 Null can be used to handle divide by zero error by passing first expression to to be denominator and 270 19:29.070 --> 19:31.020 second expression to be zero. 271 19:32.490 --> 19:34.650 So that same thing we have seen. 272 19:37.950 --> 19:41.450 Now let's see aggregation function. 273 19:44.120 --> 19:46.340 Now let's see aggregation function. 274 19:46.850 --> 19:49.070 So these are mostly the special function. 275 19:49.100 --> 19:52.040 Null if is null coalesce. 276 19:53.600 --> 19:57.860 Most of the time we are using these kind of functions only. 277 19:57.950 --> 20:00.650 Now see what are the aggregation functions. 278 20:00.680 --> 20:03.710 So mostly sum max min average and count. 279 20:03.950 --> 20:11.210 So aggregation functions operate on set of rows to calculate a return a single or scalar value. 280 20:11.240 --> 20:11.840 Right. 281 20:13.490 --> 20:16.790 This all functions are returning. 282 20:16.820 --> 20:17.570 Ignore nulls. 283 20:17.570 --> 20:18.200 Right. 284 20:19.070 --> 20:21.170 This all functions will return. 285 20:21.560 --> 20:22.820 Ignore nulls. 286 20:23.210 --> 20:24.830 Now count of column. 287 20:24.830 --> 20:28.790 So count of column will ignore null in count of star. 288 20:28.820 --> 20:30.080 Consider nulls. 289 20:33.650 --> 20:36.890 Now I think this table is already created. 290 20:36.890 --> 20:39.210 Let me try to show you the data. 291 20:45.600 --> 20:55.260 Select star from this one aggregation table so it has a data like this, right? 292 20:55.350 --> 20:58.050 Ten 2010 2030. 293 20:58.080 --> 20:58.680 Null. 294 21:00.210 --> 21:03.840 So I have already written over here now. 295 21:10.290 --> 21:12.600 Now sum of all value. 296 21:12.600 --> 21:15.450 So it is nothing just like this one. 297 21:15.480 --> 21:17.610 And this one will be same. 298 21:17.610 --> 21:21.450 Only if we mention all then also fine. 299 21:21.450 --> 21:26.550 If we do not mention all, then also it will do the sum of all these values. 300 21:26.550 --> 21:27.990 So what will be the sum? 301 21:28.890 --> 21:34.620 30 plus 20, 50, 60, 70, 80 and 90. 302 21:35.040 --> 21:39.610 So in both of these case it will give me 90, right? 303 21:39.910 --> 21:43.060 In both of these case, it will give me 90. 304 21:52.900 --> 21:56.830 Now what should it will give me over here some of distinct value. 305 21:56.860 --> 22:02.620 So 30 will come 150, 60, 70, 80. 306 22:02.650 --> 22:04.120 This ten will not come again. 307 22:04.120 --> 22:04.720 Right. 308 22:04.930 --> 22:06.970 So it should give me 80. 309 22:08.050 --> 22:08.470 Sorry. 310 22:08.470 --> 22:12.580 Sorry 30 plus 20, 50 and 60. 311 22:12.610 --> 22:14.080 It should give me 60. 312 22:14.170 --> 22:16.630 This 20 is also repeating over here. 313 22:16.630 --> 22:20.470 So we need to calculate only distinct value. 314 22:20.500 --> 22:21.610 It should give me 60. 315 22:21.640 --> 22:22.210 Right. 316 22:28.120 --> 22:29.740 Now again move ahead. 317 22:30.550 --> 22:37.700 Now I have created this table sales table let's see the data. 318 22:37.730 --> 22:40.970 I think I have already copied the data over here. 319 22:42.290 --> 22:43.880 So this is the data. 320 22:44.540 --> 22:45.530 See over here. 321 22:47.900 --> 22:50.030 Order ID product quantity. 322 22:50.060 --> 22:51.170 Unit price. 323 22:51.950 --> 22:52.790 Now. 324 23:00.140 --> 23:02.750 Now let's see a very interesting thing. 325 23:02.930 --> 23:04.700 What it will give me. 326 23:05.480 --> 23:09.680 So let's see what it will give me. 327 23:13.760 --> 23:14.300 Mm. 328 23:17.930 --> 23:18.320 Okay. 329 23:18.320 --> 23:21.020 Let me do over here. 330 23:21.020 --> 23:23.690 It should be quantity multiplied by unit price. 331 23:23.690 --> 23:23.870 Right. 332 23:23.900 --> 23:26.750 So over here 1000. 333 23:29.180 --> 23:31.010 Then over here 500. 334 23:31.880 --> 23:32.600 Then. 335 23:32.630 --> 23:38.310 Then again over here 500 and over here. 336 23:43.290 --> 23:44.100 400. 337 23:44.190 --> 23:45.840 So 1500. 338 23:47.850 --> 23:49.080 2400. 339 23:49.080 --> 23:49.410 Right. 340 23:49.440 --> 23:51.390 It should give me 2400. 341 23:51.420 --> 23:52.230 Let's see. 342 23:54.600 --> 23:55.230 Perfect. 343 23:55.260 --> 23:55.860 Right. 344 23:56.130 --> 24:00.930 We are doing multiplication of quantity and unit price. 345 24:00.930 --> 24:05.190 But now see what will give me over here this time. 346 24:07.440 --> 24:12.900 So over here also it has done quantity and unit price quantity multiplied by unit price. 347 24:12.960 --> 24:19.440 But if you see over here 505 multiplied by 101 multiplied by 500. 348 24:19.500 --> 24:23.220 So it will consider only this one 1000. 349 24:24.120 --> 24:28.770 Then out of this 500 it will only only consider one 500. 350 24:29.070 --> 24:35.200 And this four 400 it will not consider this one. 351 24:35.380 --> 24:35.590 Why? 352 24:35.620 --> 24:37.420 Because it is a distinct. 353 24:37.750 --> 24:42.580 We are only considering distinct of this combination. 354 24:42.580 --> 24:45.970 We are considering only distinct of this combination. 355 24:45.970 --> 24:49.060 So it will give me 1900, right? 356 24:50.650 --> 24:52.540 It will give me 1900. 357 24:52.570 --> 24:53.200 Right. 358 25:01.210 --> 25:03.010 Now let's see. 359 25:05.110 --> 25:10.150 So what will be the average of value in this particular table. 360 25:10.150 --> 25:19.000 So let's see now again select star from this table average of value. 361 25:19.000 --> 25:20.020 So. 362 25:22.840 --> 25:29.380 30 2050 6070 8090 and 90. 363 25:29.680 --> 25:33.930 So select 90 divided by five. 364 25:37.200 --> 25:38.340 18 right. 365 25:38.340 --> 25:47.130 So over here it will give me 18 because aggregate function will always ignore the null values. 366 25:47.160 --> 25:54.390 It will not consider like in this table six values are there right in this table six values are there. 367 25:54.390 --> 26:01.650 So it will not consider over here six it will ignore that null value and it will consider only five. 368 26:03.690 --> 26:10.110 Now see another example we have to consider distinct only right. 369 26:10.110 --> 26:18.480 So average of distinct 3020, 5060 and 60 divided by. 370 26:21.330 --> 26:24.450 Five it has taken 60 divided by five. 371 26:25.500 --> 26:26.520 So. 372 26:28.860 --> 26:30.360 Sorry one minute. 373 26:33.550 --> 26:38.260 It is given, it is done 60 divided by three only, not five. 374 26:38.530 --> 26:39.250 Why? 375 26:39.280 --> 26:42.040 Because it will consider distinct value, right? 376 26:42.070 --> 26:45.790 One, two and three distinct values are there. 377 26:46.480 --> 26:51.460 And what is the total total of these three value is 50 and 60. 378 26:52.090 --> 26:54.370 So 60 divided by. 379 26:57.580 --> 27:03.340 Three only it will do this time and it will give me result 20. 380 27:05.500 --> 27:06.310 Right. 381 27:09.490 --> 27:11.950 Now let's see the max function. 382 27:11.950 --> 27:14.920 So Max will also give the same. 383 27:17.170 --> 27:22.600 Even though you are use distinct or not distinct, it will not affect. 384 27:22.630 --> 27:26.320 Now let me try to use max of Ename. 385 27:27.040 --> 27:34.790 So whether it will work in the string or not, it will work right It will work, so it will sort the 386 27:34.790 --> 27:44.450 result into alphabetically and from highest to character like z, w, x, y, z. 387 27:44.930 --> 27:54.320 So that will be the max value starting name with this particular character it will give me the max value. 388 27:54.410 --> 27:56.030 Now max of higher date. 389 27:56.270 --> 27:58.490 So this is also a simple one. 390 27:58.580 --> 28:00.680 Maximum higher date it will give me. 391 28:03.470 --> 28:04.760 Now minimum. 392 28:05.030 --> 28:07.580 So over here okay. 393 28:07.580 --> 28:08.240 What. 394 28:10.250 --> 28:11.780 Now let's see. 395 28:23.180 --> 28:24.170 Count of values. 396 28:24.170 --> 28:26.810 So we have five distinct value right 397 28:30.330 --> 28:33.690 So over here, one thing is also important. 398 28:34.260 --> 28:35.430 Count of star. 399 28:35.460 --> 28:38.220 So over here in this particular table. 400 28:49.320 --> 28:51.720 There are total six values are there. 401 28:52.350 --> 28:58.410 But whenever we mention count of value then value will count. 402 28:58.410 --> 29:00.000 Function will ignore the null value. 403 29:00.030 --> 29:00.600 Right. 404 29:03.030 --> 29:05.730 Count function or all the. 405 29:18.240 --> 29:20.820 So over here it will show me only five. 406 29:21.510 --> 29:31.060 But when but whenever I will mention the I will mention the Start, then it will give me the value. 407 29:31.090 --> 29:32.170 Like this one. 408 29:34.390 --> 29:35.440 Six. 409 29:36.250 --> 29:36.760 Right now. 410 29:36.790 --> 29:38.140 Count of distinct value. 411 29:38.170 --> 29:39.700 Distinct value is three. 412 29:41.170 --> 29:44.020 And now count of unit price. 413 29:48.850 --> 29:49.090 Okay. 414 29:49.090 --> 29:51.850 Let me take this result over here. 415 29:56.740 --> 30:00.220 From sales. 416 30:03.790 --> 30:04.120 Okay. 417 30:04.120 --> 30:06.310 It is giving me four four only, right? 418 30:06.310 --> 30:07.930 It is giving me four only. 419 30:09.160 --> 30:09.580 Why? 420 30:09.580 --> 30:10.450 It is giving me four. 421 30:10.480 --> 30:11.050 Let us see. 422 30:11.080 --> 30:13.240 Select star from. 423 30:14.320 --> 30:16.780 I think it should have four record only. 424 30:18.160 --> 30:18.460 Okay. 425 30:18.490 --> 30:18.880 Yes. 426 30:18.880 --> 30:19.450 Perfect. 427 30:19.480 --> 30:21.220 It is a four record only. 428 30:23.260 --> 30:26.800 Now count returns the number number of rows in a table. 429 30:26.800 --> 30:27.760 It considers none. 430 30:27.760 --> 30:30.050 nor right count of star will consider nulls. 431 30:32.420 --> 30:38.540 This one also we have seen count function when taking columns or expression as argument ignore null 432 30:38.570 --> 30:41.240 but count of star does not ignore nulls. 433 30:41.900 --> 30:42.140 Right? 434 30:42.170 --> 30:44.330 There is that thing also we have seen. 435 30:45.380 --> 30:49.400 Now it will give me the maximum salary from this particular table. 436 30:50.450 --> 30:59.510 Display the maximum salary and if I want departments department wise maximum salary. 437 30:59.630 --> 31:00.230 Right. 438 31:02.960 --> 31:05.600 Then it will give me department wise maximum salary. 439 31:06.680 --> 31:07.610 Let us see. 440 31:13.130 --> 31:13.490 Okay. 441 31:13.490 --> 31:16.700 Not department wise in the particular department. 442 31:16.730 --> 31:21.740 What is the maximum salary it will give me like that name. 443 31:21.740 --> 31:22.040 Name. 444 31:22.040 --> 31:23.450 Count of hire date. 445 31:23.990 --> 31:28.660 Let us see Where year of hire date. 446 31:28.660 --> 31:30.640 So it will be I think 1 or 2. 447 31:30.640 --> 31:32.770 Only one okay. 448 31:32.800 --> 31:33.250 One. 449 31:33.250 --> 31:46.480 So let me show you select star from employee where year of higher date equal to 2021. 450 31:47.290 --> 31:49.180 So it will give me one row only right? 451 31:49.180 --> 31:50.890 It should give me one row only. 452 31:52.000 --> 31:52.510 Perfect. 453 31:52.510 --> 31:59.800 Only one employee is higher in 2021 now count of star. 454 31:59.800 --> 32:02.830 I think also this one will give me one only. 455 32:05.620 --> 32:07.300 This one will also give me one. 456 32:10.300 --> 32:11.920 Now Sunday. 457 32:12.010 --> 32:16.630 So we need to identify display the number of employees joined on Sunday. 458 32:16.630 --> 32:17.260 Right. 459 32:18.040 --> 32:26.780 So how how we can get the Sunday for that we need to use that name function w BW of higher date. 460 32:27.890 --> 32:31.940 DW will give me a particular date of a week. 461 32:31.940 --> 32:34.580 Write date of the week. 462 32:34.610 --> 32:35.540 BW. 463 32:35.630 --> 32:38.000 So let me see. 464 32:38.510 --> 32:41.120 Only when employee has joined on Sunday. 465 32:43.340 --> 32:45.710 So how we can identify that one. 466 32:45.710 --> 32:47.180 Let me show you that one. 467 32:47.180 --> 32:50.210 Also select. 468 32:55.370 --> 32:56.000 Star. 469 32:56.000 --> 32:56.570 Comma. 470 32:56.600 --> 32:57.560 This one. 471 32:58.520 --> 32:59.480 Now see. 472 33:03.230 --> 33:04.400 So when is the Sunday? 473 33:06.140 --> 33:07.400 When is the Sunday? 474 33:10.130 --> 33:11.150 It will over here. 475 33:11.150 --> 33:11.540 Right. 476 33:11.570 --> 33:12.800 It will over here. 477 33:13.670 --> 33:18.770 So only one employee is higher on Sunday right now. 478 33:18.770 --> 33:20.540 Count of distinct job. 479 33:20.540 --> 33:21.590 So 480 33:24.690 --> 33:26.610 This is also a simple thing. 481 33:26.610 --> 33:31.050 There will be total three distinct jobs are there and average salary. 482 33:31.050 --> 33:33.570 So these are all the aggregate functions. 483 33:37.230 --> 33:41.160 Now let me unmute you. 484 33:41.190 --> 33:45.900 Let's see if you have any question you can ask. 485 33:54.510 --> 33:57.210 And now you can ask if you have any questions. 486 34:00.840 --> 34:01.800 Ah yes. 487 34:13.860 --> 34:14.160 Ah. 488 34:14.160 --> 34:14.580 What. 489 34:14.610 --> 34:15.060 Okay. 490 34:15.090 --> 34:15.840 Is null. 491 34:21.780 --> 34:22.500 Okay. 492 34:23.020 --> 34:26.980 Actually, this is a first of all, this is not a function. 493 34:27.640 --> 34:29.050 This is a operator. 494 34:31.060 --> 34:33.790 Uh, this one we can say that operator. 495 34:34.180 --> 34:36.760 And this one we can say as a function. 496 34:37.420 --> 34:38.950 Now, one more thing. 497 34:38.950 --> 34:42.700 Whenever let me show you the example. 498 34:42.730 --> 34:44.470 Select star from amp. 499 34:44.500 --> 34:44.770 Right. 500 34:44.800 --> 34:46.330 Select star from amp. 501 34:46.750 --> 34:50.860 Now I want to see where commission. 502 34:52.450 --> 34:54.160 Where there is a null commission. 503 34:54.160 --> 34:56.680 There is no employee are getting commission. 504 34:56.680 --> 34:57.280 Right. 505 34:57.310 --> 34:59.800 I want to see that record only. 506 34:59.830 --> 35:03.100 So in this particular case I can write like this. 507 35:03.130 --> 35:14.680 When commission is null then I will I will get this one all the null record. 508 35:17.140 --> 35:25.310 Now I can I think I cannot use over here like commission Is null. 509 35:25.310 --> 35:30.020 I cannot use like this over here. 510 35:30.020 --> 35:32.360 But now I want same result, right? 511 35:32.360 --> 35:33.500 I want same result. 512 35:33.530 --> 35:34.850 Now let me try. 513 35:35.960 --> 35:38.510 Select star from employee. 514 35:38.540 --> 35:44.420 Where is null of commission comma zero. 515 35:46.460 --> 35:47.660 Equal to zero. 516 35:47.750 --> 35:49.430 Now let me try. 517 35:53.030 --> 35:54.920 Again this is giving me same result. 518 35:54.920 --> 35:57.140 So this is also giving me same result. 519 35:57.950 --> 36:00.230 And this is also giving me same result. 520 36:01.460 --> 36:07.910 So I think there is yeah there is one problem because of one record has a 0.0 commission. 521 36:09.200 --> 36:13.730 So that is also displayed in in terms of second result. 522 36:13.790 --> 36:16.880 But over over here that record will not come. 523 36:17.960 --> 36:24.400 So if you see over here there are 12 records over here and it will be 13 records over here in the below 524 36:24.400 --> 36:25.090 query. 525 36:25.990 --> 36:30.490 But now one more thing we can also use like this is null. 526 36:33.550 --> 36:36.670 Zero and commission. 527 36:37.600 --> 36:41.110 So we have used like this over here here. 528 36:42.310 --> 36:48.070 What will it will give me whenever commission is null it will give me give me 0.00. 529 36:50.800 --> 36:57.100 What I have done over here whenever first argument is null is null means whenever first argument is 530 36:57.100 --> 37:03.220 null, it will give me second argument, but this is null. 531 37:03.400 --> 37:06.940 Mostly used in terms of where condition only. 532 37:10.120 --> 37:10.390 Yeah. 533 37:10.390 --> 37:11.950 When we want to filter the record. 534 37:11.980 --> 37:12.370 Yes. 535 37:12.370 --> 37:12.550 Yes. 536 37:12.550 --> 37:13.180 Right. 537 37:16.540 --> 37:19.360 And this one we can use with the case statement. 538 37:19.360 --> 37:23.570 Also we can use either this or either this. 539 37:23.570 --> 37:25.070 Anything will be fine. 540 37:27.200 --> 37:30.500 Like when case. 541 37:33.890 --> 37:34.940 Or a case when? 542 37:34.970 --> 37:35.570 Right. 543 37:35.600 --> 37:41.330 Case when condition is null, then zero. 544 37:42.050 --> 37:44.540 Else Commission end. 545 37:46.220 --> 37:48.740 Now let's see what will happen over here. 546 37:50.180 --> 37:52.430 Now it is giving me 0000. 547 37:54.590 --> 38:00.440 But over here it will give me 0000 without writing case statement. 548 38:02.510 --> 38:03.320 Right. 549 38:08.000 --> 38:09.140 Now let me ask. 550 38:09.140 --> 38:11.240 Anyone has any other question. 551 38:16.160 --> 38:22.650 The case is already covered Yeah, K7 is already covered. 552 38:26.820 --> 38:28.680 When it is covered. 553 38:32.880 --> 38:35.250 I have a simple case statement. 554 38:35.280 --> 38:37.350 Search case statement I have covered. 555 38:37.950 --> 38:38.580 I. 556 39:02.790 --> 39:08.820 Know that that is known as Windows Aggregate. 557 39:10.020 --> 39:11.430 Windows aggregate. 558 39:11.430 --> 39:11.670 In. 559 39:11.850 --> 39:15.660 In windows function, you are telling like sum over. 560 39:15.690 --> 39:15.930 Right? 561 39:15.960 --> 39:16.230 Right. 562 39:16.230 --> 39:18.150 We are using So. 563 39:18.400 --> 39:19.090 Yeah. 564 39:19.120 --> 39:19.930 So. 565 39:19.960 --> 39:24.190 And, uh, win those. 566 39:27.190 --> 39:28.210 Ranking functions. 567 39:28.210 --> 39:32.470 So over here it will come like rank 10th rank. 568 39:35.800 --> 39:38.740 Then entail then row number. 569 39:39.550 --> 39:45.670 And over here it will come sum over then maybe count over. 570 39:47.020 --> 39:49.750 So this is known as windows aggregate. 571 39:49.780 --> 39:54.040 But in simple aggregate we are not using over clause right. 572 39:56.080 --> 40:03.700 This is simple aggregate only sum max min count average. 573 40:03.730 --> 40:05.170 These are simple aggregate. 574 40:05.380 --> 40:08.530 We will cover all these in our coming lectures. 575 40:08.560 --> 40:09.160 Yeah 576 40:31.220 --> 40:40.370 So we you should, uh, first, uh, clarify that, uh, windows function is, uh, different categories. 577 40:40.460 --> 40:41.900 Now one more categories. 578 40:41.930 --> 40:46.760 Are there windows for political functions. 579 40:46.760 --> 40:50.960 So what are so you should clarify that there are multiple categories. 580 40:50.960 --> 40:55.670 Are there windows ranking, windows aggregate then windows analytical. 581 40:55.760 --> 41:00.110 And in the windows uh ranking function these functions will cover. 582 41:00.380 --> 41:08.090 Then windows aggregates sum count average max min mean of over will cover and windows analytical. 583 41:08.090 --> 41:09.650 What will cover over here? 584 41:09.740 --> 41:17.880 It will cover like uh lead lag first value, then last value. 585 41:17.910 --> 41:19.020 Like this. 586 41:19.050 --> 41:26.250 You should clarify that in windows, multiple categories are there and you can explain all this function 587 41:27.000 --> 41:28.950 in Azure Windows function. 588 41:29.130 --> 41:34.260 But when someone tell you what are the aggregate function then you have to tell like this. 589 41:34.260 --> 41:35.760 Only only this one. 590 41:50.610 --> 41:52.020 Yeah, yeah. 591 41:52.050 --> 42:00.090 Coming to syntax, uh, in terms of some and some column name, it will give me only one value, right, 592 42:00.090 --> 42:04.980 one value or group by or whatever we have mentioned in the group by. 593 42:05.280 --> 42:14.250 So if we are doing the department wise sum right, then it will do department wise sum like 30 and then 594 42:14.250 --> 42:18.470 sum of this particular department then some of this particular department. 595 42:18.500 --> 42:20.660 So it will aggregate the data. 596 42:20.660 --> 42:23.510 But over here it will not aggregate the data. 597 42:24.800 --> 42:35.030 It will uh some of over then uh, if we mentioned like over and then if we close this one, then it 598 42:35.030 --> 42:39.590 will give me the sum of entire all the rows, all the rows. 599 42:40.880 --> 42:42.380 It will perform row by row. 600 42:42.410 --> 42:45.410 It will perform, uh, row, row by row over here. 601 42:45.410 --> 42:49.910 But over here it will perform by, uh, groups. 602 42:49.910 --> 42:51.770 It will perform by groups. 603 42:54.110 --> 43:01.820 But, uh, we will when your all the doubts will be clear, when we will see this one also group by 604 43:01.820 --> 43:03.110 also we have to see right. 605 43:03.110 --> 43:05.600 It is pending group by is also pending. 606 43:07.280 --> 43:09.650 So tomorrow's lecture we will see group by. 607 43:09.680 --> 43:12.620 At that time your doubt will be more cleared 608 43:17.520 --> 43:18.030 Okay. 609 43:18.030 --> 43:18.360 Okay. 610 43:18.360 --> 43:18.870 Okay. 611 43:18.900 --> 43:19.200 Yeah. 612 43:29.250 --> 43:29.730 Okay. 613 43:29.730 --> 43:32.550 So now let's see I have created this table. 614 43:32.730 --> 43:35.490 Data underscore test table. 615 43:35.550 --> 43:38.070 So let me show you the table. 616 43:38.220 --> 43:44.730 So now over here you want the difference between start date and end date into hours and minutes. 617 43:44.760 --> 43:45.270 Right. 618 43:46.500 --> 43:49.320 So carefully see this data. 619 43:49.560 --> 43:53.370 Now I am showing you first result also. 620 43:53.370 --> 43:58.140 And you tell me whether this result is correct or not. 621 43:58.170 --> 44:02.670 Now let's let's analyze last two record. 622 44:02.700 --> 44:04.770 It will be more easier 623 44:20.530 --> 44:22.960 Now see 18 over here. 624 44:22.960 --> 44:24.130 And 11 is over here. 625 44:24.160 --> 44:25.060 Right over. 626 44:25.300 --> 44:26.740 First we have to see ours. 627 44:27.070 --> 44:29.200 So 18 -11 seven. 628 44:29.200 --> 44:29.770 Right. 629 44:31.810 --> 44:34.420 And over here, 12 minus ten two. 630 44:34.450 --> 44:35.110 Right. 631 44:36.490 --> 44:41.140 So first I have taken seven and 2 in 1 column. 632 44:43.870 --> 44:45.730 Now how many minutes. 633 44:45.730 --> 44:47.710 So over here 30 minutes. 634 44:47.740 --> 44:48.910 1130. 635 44:49.300 --> 44:51.790 And over here 20 minutes. 636 44:52.780 --> 44:54.280 So 30 plus 20. 637 44:54.310 --> 44:55.210 How much. 638 44:56.830 --> 44:57.460 50. 639 44:57.490 --> 44:59.320 I have taken in another column. 640 45:00.790 --> 45:01.990 And over here. 641 45:04.060 --> 45:05.260 Ten minutes. 642 45:09.490 --> 45:13.550 And over here, 20 minutes I think something is wrong. 643 45:15.170 --> 45:18.410 Okay, let's see now over here. 644 45:18.410 --> 45:22.880 I have done 30, but actually it is wrong. 645 45:22.910 --> 45:23.150 Why? 646 45:23.180 --> 45:23.750 Why? 647 45:24.680 --> 45:26.930 So in this case, 30. 648 45:27.380 --> 45:29.270 In 12 30 minutes will be left. 649 45:29.270 --> 45:29.870 Right. 650 45:32.600 --> 45:33.920 But over here. 651 45:33.950 --> 45:35.150 1010 is there. 652 45:35.150 --> 45:39.200 So how many minutes are left? 653 45:43.250 --> 45:43.520 No. 654 45:43.520 --> 45:44.390 No, ten. 655 45:44.420 --> 45:46.670 Ten means in one hour. 656 45:46.700 --> 45:47.150 11. 657 45:47.180 --> 45:48.770 How many minutes are left? 658 45:49.130 --> 45:49.970 40 minutes. 659 45:50.000 --> 45:50.300 Right. 660 45:50.330 --> 45:51.110 40. 661 45:51.200 --> 45:51.860 40. 662 45:51.890 --> 45:52.130 Okay. 663 45:52.160 --> 45:52.520 50. 664 45:52.550 --> 45:52.880 50. 665 45:52.910 --> 45:53.450 Right. 666 45:54.410 --> 45:55.490 Okay, 50. 667 45:55.520 --> 45:57.830 So over here. 668 45:58.880 --> 45:59.960 It should be over here. 669 45:59.960 --> 46:00.380 Also. 670 46:00.410 --> 46:00.860 50. 671 46:00.890 --> 46:01.460 Right? 672 46:02.450 --> 46:02.840 Oh, no. 673 46:02.840 --> 46:04.370 No, 50 plus 20. 674 46:04.400 --> 46:05.660 70. 675 46:17.010 --> 46:18.660 Uh, it should be. 676 46:19.530 --> 46:21.000 It should be adding. 677 46:21.000 --> 46:21.360 Right? 678 46:21.390 --> 46:22.470 We should add. 679 46:28.050 --> 46:30.300 Actually, in this case, our result set. 680 46:30.330 --> 46:37.230 Set is correct in this case because 18 -11. 681 46:37.230 --> 46:38.190 So seven hours. 682 46:38.220 --> 46:38.940 Right. 683 46:38.970 --> 46:42.630 And then 30 minutes this will left 30 minutes. 684 46:42.630 --> 46:45.690 And we need to add this 30 into this 20. 685 46:45.690 --> 46:50.070 So it will be 57 hours and 50 minutes. 686 46:50.790 --> 46:55.230 So now let me show you the query. 687 46:56.220 --> 47:01.530 I have taken date diff date diff between these two particular dates in hours. 688 47:05.100 --> 47:06.480 Date difference in hours. 689 47:06.510 --> 47:09.720 Then what I have done, I have done date part. 690 47:09.790 --> 47:10.150 Date. 691 47:10.150 --> 47:11.260 Part in minutes. 692 47:11.290 --> 47:13.960 Start date and end date separately. 693 47:18.370 --> 47:19.180 And. 694 47:20.110 --> 47:20.530 Yeah. 695 47:20.560 --> 47:20.830 I'm. 696 47:23.290 --> 47:24.850 And then adding it. 697 47:26.440 --> 47:29.830 But I am still feeling this is a wrong result. 698 47:29.860 --> 47:33.520 Then what I am doing over here, I am just doing concatenating. 699 47:33.910 --> 47:36.280 Concatenating three things over here. 700 47:36.310 --> 47:37.540 Date diff hours. 701 47:38.980 --> 47:40.360 Uh, hours. 702 47:40.780 --> 47:43.360 Then I have to see, uh, ten. 703 47:43.390 --> 47:45.130 It will give me like ten, right? 704 47:45.250 --> 47:46.420 Or seven. 705 47:46.630 --> 47:48.700 Then I want like hours end. 706 47:48.700 --> 47:50.920 So seven hours end. 707 47:50.950 --> 47:52.000 How many minutes. 708 47:52.060 --> 47:58.240 So it will give me minutes like something 20 and 20 minutes. 709 48:03.730 --> 48:03.910 Yeah. 710 48:03.940 --> 48:13.110 But yeah I have yeah I have already calculated but I want in a separate column right over here. 711 48:13.110 --> 48:13.950 We have already. 712 48:13.980 --> 48:17.130 Calculated over here we have already calculated. 713 48:17.130 --> 48:19.920 But I want like this in a separate column. 714 48:25.050 --> 48:31.230 But I am still having a doubt that this is a wrong, uh, in place of this one. 715 48:31.230 --> 48:32.520 It is a wrong result. 716 48:32.520 --> 48:34.860 I think in this one it is correct one. 717 48:39.570 --> 48:41.580 But whenever the. 718 48:41.610 --> 48:44.670 But over here in the fourth row, it is wrong, I think. 719 48:46.860 --> 48:48.210 You know why? 720 49:03.540 --> 49:04.110 Yeah. 721 49:04.110 --> 49:06.210 I am extracting the minutes. 722 49:06.240 --> 49:07.080 Yes, yes 723 49:16.450 --> 49:16.810 Mm. 724 49:16.840 --> 49:17.320 Yeah. 725 49:17.470 --> 49:21.430 That time different in minutes maybe. 726 49:21.430 --> 49:22.360 Okay. 727 49:28.570 --> 49:28.960 Okay. 728 49:28.960 --> 49:36.400 You can or you can try in your data and see that whether this is giving correct result or not I will 729 49:36.430 --> 49:40.240 I will give you create table, insert table and uh script. 730 49:40.240 --> 49:44.920 All this script you can try in your data set. 731 49:51.520 --> 49:52.900 You can copy paste. 732 49:55.120 --> 49:56.770 You can further experiment. 733 49:56.770 --> 49:59.500 If it is wrong it is giving wrong result. 734 50:00.340 --> 50:03.760 Then we may further discuss or you can experiment more