1 00:02.450 --> 00:07.940 Okay, so today we are covering from the group by having clothes and other things. 2 00:08.660 --> 00:11.780 So let's start start with there. 3 00:12.290 --> 00:18.110 So basically let's start uh what why we are doing the group by thing. 4 00:18.110 --> 00:19.820 Why we are doing that one. 5 00:20.210 --> 00:24.020 So currently we have this employee table with us. 6 00:24.020 --> 00:24.500 Right. 7 00:24.530 --> 00:26.810 We have this employee table with us. 8 00:26.990 --> 00:31.220 And if we want to do some analysis from this particular table. 9 00:31.310 --> 00:35.180 So let me copy this data into the Excel file. 10 00:36.440 --> 00:41.090 And if we want to do some some analysis from this table like. 11 00:43.160 --> 00:50.480 How many different how many employees are working per different job. 12 00:50.510 --> 00:51.290 Job. 13 00:51.290 --> 00:54.380 So over here we have how many jobs are there. 14 00:54.530 --> 00:58.580 So let me check that one. 15 01:02.420 --> 01:11.750 Can I delete duplicate data from this column, remove duplicates column and okay so we have total five 16 01:11.750 --> 01:13.520 different jobs are there right. 17 01:13.550 --> 01:16.370 We have total five different jobs are there. 18 01:16.370 --> 01:20.690 And how many employees are working in each job category. 19 01:21.830 --> 01:26.750 So we have to ideally select this one manager. 20 01:26.900 --> 01:29.090 So this one is one one employee. 21 01:29.090 --> 01:32.300 Then we can say this one is another employee. 22 01:32.300 --> 01:35.030 Then we these two are another employee. 23 01:36.320 --> 01:43.220 And maybe we can say four employees are working in the category of uh manager job category. 24 01:43.250 --> 01:43.820 Right. 25 01:44.630 --> 01:53.990 So that we can say one analysis, then we can say how many department wise how many employees are working. 26 01:54.080 --> 01:58.350 So we have total ten, 20 and 30 departments are there. 27 01:58.350 --> 02:04.830 So in department ten, how many employees are working suppose for in department 20, how many employees 28 02:04.830 --> 02:05.700 are working? 29 02:05.700 --> 02:06.840 Suppose 20. 30 02:06.870 --> 02:11.940 So doing this kind of analysis we are using group by right. 31 02:14.250 --> 02:18.750 Doing this kind of analysis we are doing using group by then. 32 02:18.750 --> 02:23.760 One more example we can say in the year wise how many employees are joined. 33 02:23.760 --> 02:26.730 So in that case we have to use higher rate, right. 34 02:26.970 --> 02:31.770 We have to use higher rate and we have to do year of higher rate. 35 02:31.800 --> 02:38.880 If we do year of higher rate, then it will give me like something like this 1981, then 2000, then 36 02:38.880 --> 02:47.250 2002, then 2020, then over here, if we say seven employees are there, over here, three over here, 37 02:47.250 --> 02:48.510 five over six. 38 02:49.080 --> 02:52.560 So here wise, how many employees are working. 39 02:52.560 --> 02:57.090 So doing this kind of different A different analysis. 40 02:57.360 --> 03:00.870 We are doing the group by right now. 41 03:00.870 --> 03:02.760 Let's jump to the SQL. 42 03:03.900 --> 03:09.600 So group by clause is used to convert the detailed data. 43 03:09.630 --> 03:12.630 So what will be considering this one. 44 03:12.630 --> 03:16.950 This will be considering as a detailed data right. 45 03:16.980 --> 03:21.690 Detailed data into summarized data into summarized data. 46 03:21.720 --> 03:23.910 So what we can say this one. 47 03:24.360 --> 03:25.800 What we can say this one. 48 03:25.800 --> 03:29.130 This one is can say is the summarized data right. 49 03:29.280 --> 03:32.370 Summarized data for every group. 50 03:32.580 --> 03:33.180 Group. 51 03:33.180 --> 03:35.610 Group by will return only one row. 52 03:36.450 --> 03:40.530 So over here for ten this is a one group right. 53 03:40.560 --> 03:44.700 So for this uh, it will return only one row for 20. 54 03:44.730 --> 03:47.250 It will return only one row for 30 Department. 55 03:47.250 --> 03:49.890 It will return only one row like that. 56 03:49.890 --> 03:50.550 Right. 57 03:50.610 --> 03:53.760 So now only. 58 03:53.760 --> 03:56.710 So display the department wise total salaries. 59 03:57.640 --> 04:01.240 So over here what is my requirement over here? 60 04:01.330 --> 04:05.620 My requirement is 2010, 20 and 30. 61 04:05.650 --> 04:08.860 We have total three departments are there right. 62 04:09.010 --> 04:12.490 And I want to find department wise total salary. 63 04:12.520 --> 04:17.350 So I have to do some of this particular salary and department wise. 64 04:18.460 --> 04:20.170 So how it will work. 65 04:20.800 --> 04:25.150 So we have to do select department them sum. 66 04:25.180 --> 04:26.740 Then sum of salary. 67 04:27.070 --> 04:28.720 This is alias we have given. 68 04:28.720 --> 04:29.140 Right. 69 04:29.140 --> 04:35.950 This is alias we have given from EMP table and group by group by dept. 70 04:36.220 --> 04:39.490 So whenever we say that department wise. 71 04:39.640 --> 04:49.300 So whatever wise or each department or each each job at that time, that particular column we have to 72 04:50.050 --> 04:55.510 will be appearing over here in the select statement, then we have to do aggregate. 73 04:55.540 --> 04:56.320 Yeah, right. 74 04:56.860 --> 04:57.760 Total salary. 75 04:57.790 --> 04:59.110 We want total salary. 76 04:59.110 --> 05:01.900 That is why we have done some of salary over here. 77 05:03.040 --> 05:10.150 And now if I execute this one it will give me a result. 78 05:10.180 --> 05:11.800 No it will not give me result. 79 05:11.830 --> 05:13.330 What error it is thrown. 80 05:13.810 --> 05:21.610 Column MP is invalid in the select list because it is not contained in either aggregate function or 81 05:21.610 --> 05:22.810 group by clause. 82 05:23.170 --> 05:29.110 So whatever the column is written over here, whatever, how many number of columns are there? 83 05:29.230 --> 05:32.830 Either it will be there in the group by clause. 84 05:33.670 --> 05:40.600 Either it will be there in the group by clause, or either it will be there in the aggregate function. 85 05:44.230 --> 05:44.710 Right. 86 05:44.710 --> 05:48.370 So I cannot write like this job. 87 05:51.050 --> 05:54.350 If I execute this one, it will give me error. 88 05:56.690 --> 05:57.650 Okay. 89 05:57.950 --> 06:00.170 We need to mention comma over here. 90 06:01.100 --> 06:03.260 Now again this will give me error. 91 06:04.100 --> 06:07.190 This time it is giving me even though I have used group by. 92 06:07.850 --> 06:10.670 But it is related to job related error right? 93 06:10.700 --> 06:13.760 Because I have to mention job over here as well. 94 06:16.640 --> 06:17.960 Or what can I do? 95 06:17.990 --> 06:24.950 Or I can do is I have to retrieve like this max of job. 96 06:30.050 --> 06:37.880 So either I have to use aggregate function or I have to use in the group by statement. 97 06:38.450 --> 06:40.670 Now let's move forward. 98 06:43.760 --> 06:46.670 Display job wise number of employees. 99 06:46.700 --> 06:52.790 So over here what I have mentioned in particular job how many employees are working. 100 06:52.850 --> 06:56.180 So for in manager total four employees are working. 101 06:56.210 --> 07:00.140 Then in Clark suppose two employees are working or three employees are working. 102 07:00.230 --> 07:01.670 So like this. 103 07:01.670 --> 07:05.870 So at that time also select job then count of star. 104 07:06.740 --> 07:14.600 Or you can say count of employee number as well as count from employee group by job. 105 07:17.750 --> 07:20.120 As count from employee group by job. 106 07:20.120 --> 07:24.260 So what it will give me, it will give me the result like this. 107 07:34.310 --> 07:36.020 It will give me the result like this. 108 07:36.020 --> 07:38.960 So see over here manager, we have seen over here right? 109 07:38.990 --> 07:45.980 Total one, two, three and four employees are working right in manager then clerk one. 110 07:48.980 --> 07:51.200 Clerk one, then two. 111 07:51.230 --> 07:51.860 Three. 112 07:55.400 --> 07:56.000 Then four. 113 07:56.030 --> 07:56.870 Five, six. 114 07:56.870 --> 08:01.460 So in clerk total, six employees are working, right? 115 08:03.230 --> 08:04.640 Then manager four. 116 08:04.670 --> 08:05.360 We have seen. 117 08:05.390 --> 08:06.170 Analyst. 118 08:06.410 --> 08:06.950 Analyst. 119 08:06.980 --> 08:08.540 How many employees are working? 120 08:08.930 --> 08:11.540 There are two employees are working as analyst. 121 08:11.570 --> 08:12.290 Right. 122 08:16.970 --> 08:20.120 So this is the purpose of group by clause. 123 08:21.020 --> 08:25.280 Now let's move ahead now. 124 08:25.310 --> 08:26.150 Display. 125 08:27.920 --> 08:28.730 Display. 126 08:28.760 --> 08:29.600 Display. 127 08:30.890 --> 08:31.730 Year wise. 128 08:31.730 --> 08:34.610 Number of employees joined. 129 08:34.790 --> 08:35.240 Display. 130 08:35.270 --> 08:35.810 Year wise. 131 08:35.810 --> 08:37.670 Number of employees joined. 132 08:37.970 --> 08:40.970 So that example we have seen over here right. 133 08:41.000 --> 08:43.730 So over here we have to do year of higher date. 134 08:43.760 --> 08:47.730 We have given the alias of this particular column here. 135 08:47.760 --> 08:55.140 Then count of star as count from EMP, then group by year of. 136 08:55.290 --> 09:00.870 So whatever is used over here, that one we have to write over here only. 137 09:03.780 --> 09:05.340 That one we have to write over here. 138 09:05.340 --> 09:06.750 But now suppose. 139 09:07.260 --> 09:11.550 Now suppose if I write group by year. 140 09:13.860 --> 09:15.030 Will it work? 141 09:21.360 --> 09:22.230 Will it work? 142 09:22.230 --> 09:23.490 It will not work. 143 09:23.880 --> 09:27.420 It will not work because of this year column. 144 09:27.420 --> 09:29.640 It will not able to identify. 145 09:30.450 --> 09:31.140 Why? 146 09:31.140 --> 09:32.220 Because. 147 09:33.210 --> 09:34.650 Group by clause. 148 09:34.680 --> 09:36.180 Group by clause. 149 09:41.760 --> 09:46.260 Is executed Before the. 150 09:51.750 --> 09:52.710 Select clause. 151 09:52.710 --> 09:55.860 Group by clause is executed before the Select clause. 152 09:55.890 --> 10:03.990 So see over here what is written over here from where group by having select. 153 10:04.200 --> 10:09.900 So this group by clause is executed before the select clause. 154 10:09.990 --> 10:12.870 This is the order of execution we can say. 155 10:15.570 --> 10:20.250 And this is a order which we have mentioned in the query. 156 10:28.830 --> 10:33.330 At the time of writing the query. 157 10:35.190 --> 10:35.760 Right. 158 10:35.760 --> 10:40.260 So we have to see this order when we are considering the execution. 159 10:40.830 --> 10:41.070 Right. 160 10:41.100 --> 10:44.470 So that is why this will not work over here. 161 10:44.470 --> 10:45.910 So let me. 162 10:48.250 --> 10:49.210 Comment this one. 163 10:49.210 --> 10:50.470 Let's move ahead. 164 10:51.190 --> 10:56.620 Now display the department in which more than more than five employees are working. 165 10:57.250 --> 11:00.490 So now over here having clause will come in the picture. 166 11:00.520 --> 11:01.120 Why. 167 11:02.380 --> 11:09.070 So if you see over here see the result of this particular particular result set right. 168 11:09.460 --> 11:21.910 So whenever we are doing filtering based upon the based upon the group when whenever we are. 169 11:24.160 --> 11:40.150 Doing the filtering based upon the group of rows at that time we are using having clause not the where 170 11:40.180 --> 11:40.900 clause. 171 11:41.140 --> 11:43.270 So where close when we are using. 172 11:43.300 --> 11:47.500 When we are using the filtering for for each row. 173 11:47.530 --> 11:56.620 So suppose in this Excel sheet, if I want to do some filter, if I want to do some filter then filter 174 11:56.620 --> 11:57.820 on all of the rows. 175 11:57.820 --> 12:03.430 If I want to do the filter on all of the rows at that time, very close will be used. 176 12:03.850 --> 12:06.640 But now these are the summarized data. 177 12:06.670 --> 12:10.660 This these are all the summarized data. 178 12:10.660 --> 12:17.320 And if I want to do the filter on the summarized data at that time I have to use the having clause. 179 12:17.410 --> 12:18.490 So see over here. 180 12:18.520 --> 12:28.120 Now carefully my requirement is to display the department in which more than five employees are working. 181 12:28.630 --> 12:32.980 Display the department in which more than five employees are working. 182 12:33.010 --> 12:39.640 So at that time, I have to mention over here I want this two rows only, right. 183 12:39.700 --> 12:41.510 So from this result set. 184 12:43.880 --> 12:46.490 Where more than five employees are working. 185 12:47.690 --> 12:51.170 Department number 20 and department number 30. 186 12:51.200 --> 12:51.800 Right. 187 12:54.920 --> 12:56.690 Note the department number four. 188 12:56.720 --> 12:57.380 Right. 189 12:58.310 --> 13:02.510 So at that time, I have to mention like this. 190 13:04.670 --> 13:07.250 Count of department or count of star. 191 13:07.250 --> 13:09.890 Let me try to use count of star. 192 13:10.760 --> 13:11.150 Yeah. 193 13:11.180 --> 13:11.690 Perfect. 194 13:11.690 --> 13:13.160 It is giving me perfect result. 195 13:13.190 --> 13:13.790 Right. 196 13:13.910 --> 13:15.440 20 and 30. 197 13:16.610 --> 13:20.690 I can use either column name or I can use star over here. 198 13:25.850 --> 13:31.130 So what it will do, it will do filtering on the grouping. 199 13:31.460 --> 13:32.840 These are the grouping right. 200 13:32.840 --> 13:38.630 And on this grouping when we want to do the filter at that time we have to use the having clause. 201 13:41.030 --> 13:42.020 To filter the groups. 202 13:42.050 --> 13:45.320 Create groups created by group by clause. 203 13:45.350 --> 13:47.240 We will use having clause. 204 13:47.270 --> 13:48.680 Having clause filter. 205 13:48.680 --> 13:52.880 The groups having clause can have aggregate function. 206 13:53.930 --> 13:56.300 So this is the aggregate function right? 207 13:56.330 --> 14:00.890 In the having clause we can have the aggregate function right. 208 14:02.750 --> 14:05.120 That is what it is written over here. 209 14:10.790 --> 14:11.270 Right. 210 14:11.270 --> 14:12.530 Let's move ahead. 211 14:15.620 --> 14:20.060 Display the department in the number of employees in each department. 212 14:21.560 --> 14:22.910 I am reading again. 213 14:23.300 --> 14:27.920 Display the department in the number of employees in each department. 214 14:28.820 --> 14:35.780 So whenever there is a word like each department, each category, each product at that time, that 215 14:35.780 --> 14:38.870 for that particular word we need to do grouping. 216 14:38.900 --> 14:42.680 So over here we need to do grouping based upon department. 217 14:42.710 --> 14:43.340 Right. 218 14:46.520 --> 14:51.140 But only those departments whose total salary is above 10,000. 219 14:59.570 --> 15:04.310 But whose total salary is above 10,000. 220 15:04.340 --> 15:08.120 So let me try to. 221 15:21.770 --> 15:22.370 Okay. 222 15:22.370 --> 15:25.070 So now let me try to execute this query. 223 15:25.160 --> 15:27.530 So this is the result set right. 224 15:27.530 --> 15:28.970 This is the result set. 225 15:32.150 --> 15:35.670 And now again I want to filter from this result set. 226 15:36.330 --> 15:38.610 So Department ten total salary. 227 15:38.610 --> 15:41.130 So let me show you one thing. 228 15:41.130 --> 15:42.210 What I have to do. 229 15:42.210 --> 15:45.300 I have to do ordering based upon this column. 230 15:46.320 --> 15:47.910 Where is the short? 231 15:51.990 --> 15:58.200 The short based upon based upon column number k. 232 15:58.230 --> 15:59.760 So let me do. 233 16:01.770 --> 16:02.460 Okay. 234 16:02.520 --> 16:04.620 Now what is the total salary? 235 16:04.650 --> 16:10.650 Salary of A I think this column is went last. 236 16:26.190 --> 16:26.760 Okay. 237 16:26.790 --> 16:29.100 Now this is the department column right. 238 16:29.100 --> 16:30.900 This is the department column. 239 16:30.990 --> 16:34.170 And I have to identify department by wise total salary. 240 16:34.200 --> 16:34.710 Right? 241 16:34.740 --> 16:38.220 So what is the total salary of this particular department? 242 16:40.950 --> 16:42.360 Department number ten. 243 16:42.360 --> 16:43.710 Total salaries. 244 16:47.400 --> 16:47.640 Why? 245 16:47.640 --> 16:48.990 It is coming. 246 16:54.240 --> 16:54.810 Okay. 247 16:55.830 --> 16:56.220 I. 248 17:05.820 --> 17:06.690 One minute. 249 17:19.350 --> 17:19.770 Okay. 250 17:19.770 --> 17:20.910 This is the correct. 251 17:20.910 --> 17:22.860 Uh, let me copy this one. 252 17:32.440 --> 17:36.130 So now I need to identify the total salary for each department. 253 17:36.160 --> 17:36.490 Right. 254 17:36.490 --> 17:39.940 I need to identify the total salary for each department. 255 17:40.000 --> 17:41.650 So what is the total salary? 256 17:41.860 --> 17:43.660 Salary for department number. 257 17:46.720 --> 17:48.220 20, 20 and 30. 258 17:48.250 --> 17:50.770 So I need to copy this result set. 259 17:54.250 --> 17:57.250 I copied and I am copying over here. 260 17:57.280 --> 17:58.900 Now see over here. 261 17:59.860 --> 18:01.180 Department number ten. 262 18:01.180 --> 18:01.870 Total salary. 263 18:01.870 --> 18:03.850 You can see in the bottom in the Excel right. 264 18:03.880 --> 18:07.030 Sum 10150. 265 18:07.060 --> 18:10.900 So that is what we written over here in department number 20. 266 18:10.930 --> 18:16.960 Total salary is 17457 right in the Excel file. 267 18:16.990 --> 18:17.650 Bottom side. 268 18:17.650 --> 18:18.250 Right. 269 18:18.310 --> 18:20.170 It is mentioned over here. 270 18:20.440 --> 18:24.460 So that same thing is mentioned over here in department 30. 271 18:24.580 --> 18:27.790 Total salary is 9970. 272 18:27.850 --> 18:33.640 Now what is my requirement I want to highlight what that record was. 273 18:33.640 --> 18:34.540 Total salary. 274 18:34.540 --> 18:38.590 That department whose total salary is greater than 10,000. 275 18:38.620 --> 18:39.160 Right. 276 18:39.310 --> 18:41.620 So this 30 should not come. 277 18:41.710 --> 18:46.900 And I want to do the filtering based upon this particular summarized data. 278 18:46.930 --> 18:47.470 Right. 279 18:47.500 --> 18:52.720 So at that time I have to use the group or group by and having clause right. 280 18:53.380 --> 18:55.750 So that is what is done over here. 281 18:56.050 --> 18:59.980 Having sum of salary should be greater than 10,000. 282 19:00.760 --> 19:06.070 So what it is showing it is showing me department number ten and uh 20. 283 19:06.100 --> 19:06.670 Right. 284 19:08.260 --> 19:09.310 Perfect. 285 19:11.530 --> 19:19.990 Now let's see few more example display job wise number of employees who are working as clerk and manager. 286 19:21.940 --> 19:28.960 So I want job wise number of employees who is working as clerk and manager only. 287 19:28.990 --> 19:36.280 So over here, from this particular table, I have to firstly filter the clerk record and manager record. 288 19:36.280 --> 19:36.880 Right. 289 19:37.300 --> 19:40.210 I have to filter the clerk record and manager record. 290 19:40.210 --> 19:45.070 And from that clerk and manager only I want to do grouping. 291 19:45.370 --> 19:48.940 So that is why I have used over here where clause. 292 19:50.650 --> 19:53.500 That is why I have used over here the where clause. 293 19:53.740 --> 20:01.480 Because first I want to filter clerk and manager and then I want to count how many classes are there 294 20:01.480 --> 20:05.410 in my table and how many managers are there in my table. 295 20:05.440 --> 20:09.100 So over here we do not need to use the having clause. 296 20:09.100 --> 20:09.700 Right? 297 20:10.570 --> 20:13.420 Over here we do not need to use the having clause. 298 20:15.550 --> 20:17.230 So perfect. 299 20:19.270 --> 20:21.550 There are six clerks are there. 300 20:23.200 --> 20:24.340 It is written over here. 301 20:24.370 --> 20:25.990 Six clerks are there. 302 20:26.500 --> 20:27.440 And manager. 303 20:27.440 --> 20:30.080 I think four managers are there, right? 304 20:31.040 --> 20:31.880 Perfect. 305 20:32.390 --> 20:34.760 Now let's see one more example. 306 20:36.920 --> 20:43.130 Display that department by department wise within department. 307 20:43.160 --> 20:45.020 Job wise total salary. 308 20:45.380 --> 20:46.400 So see over here. 309 20:46.400 --> 20:52.490 Read this statement once again what it is telling. 310 20:53.030 --> 20:57.620 Display department wise and within that department job wise. 311 20:58.370 --> 21:02.690 So over here we need to do grouping based upon two columns. 312 21:02.690 --> 21:06.410 We want to do department wise as well as job wise. 313 21:07.310 --> 21:13.070 Total salary from EMP group by group by this one and order by. 314 21:13.310 --> 21:17.660 This is your choice if you want to do order by line also, fine. 315 21:17.660 --> 21:20.750 If you don't want to do order by then also fine. 316 21:20.840 --> 21:24.830 So now it will give me department wise and job wise also. 317 21:26.870 --> 21:28.820 So in ten department. 318 21:28.850 --> 21:32.330 How many clerks are working in ten department? 319 21:32.360 --> 21:35.420 Only one clerk is working. 320 21:35.420 --> 21:36.800 What is their total? 321 21:38.000 --> 21:38.630 Sorry. 322 21:38.630 --> 21:40.700 Ten department. 323 21:40.790 --> 21:42.230 I have to see over here. 324 21:42.260 --> 21:42.980 Okay. 325 21:43.790 --> 21:44.570 Ten department. 326 21:44.600 --> 21:46.190 How many clerks are working? 327 21:46.490 --> 21:48.080 Ten department. 328 21:49.190 --> 21:49.670 Total. 329 21:49.670 --> 21:51.020 Two clerks are working. 330 21:51.020 --> 21:53.270 So I have to do some of this one. 331 21:53.300 --> 21:57.800 1300 and 1432 700. 332 21:57.830 --> 21:58.400 Right. 333 21:58.760 --> 22:01.160 So that is 2700. 334 22:05.180 --> 22:05.510 Ten. 335 22:05.510 --> 22:06.320 Manager. 336 22:12.530 --> 22:14.450 So department number ten. 337 22:14.480 --> 22:14.930 How many? 338 22:14.930 --> 22:16.850 Only one manager is there, right? 339 22:17.480 --> 22:18.620 I think only one. 340 22:18.770 --> 22:19.790 One manager is there. 341 22:19.790 --> 22:21.710 That is 2450. 342 22:23.940 --> 22:27.030 So this will grouping one based upon two columns. 343 22:27.960 --> 22:30.330 Now let's see one more example. 344 22:30.480 --> 22:35.160 This is the year wise number of employees joined in each department. 345 22:36.060 --> 22:37.020 See over here. 346 22:37.050 --> 22:38.970 Read over here again year wise. 347 22:38.970 --> 22:47.190 So this is one grouping year wise number of employees joined in each department. 348 22:47.220 --> 22:49.530 So we have to do one more grouping. 349 22:50.010 --> 22:53.400 Then we have to use count of star as number from EMP. 350 22:54.330 --> 22:58.380 Then group by this particular two column and order by. 351 22:58.410 --> 22:59.460 That is optional. 352 22:59.460 --> 23:02.130 If you want to do then fine. 353 23:02.130 --> 23:05.130 If you don't want to do then it is also fine. 354 23:05.310 --> 23:07.080 So this is 1980. 355 23:07.110 --> 23:14.460 Department number 21 employee is joined 1981 Department number ten. 356 23:14.460 --> 23:15.870 Two employees are joined. 357 23:15.900 --> 23:18.120 1981 Department number 20. 358 23:18.150 --> 23:21.300 Again two employees are joined like that. 359 23:23.340 --> 23:24.630 Now one more example. 360 23:24.630 --> 23:28.560 Let's say display year wise number of employees join in each department. 361 23:28.560 --> 23:34.350 But only those results were minimum two employees are joined. 362 23:36.360 --> 23:39.930 So this is the same query we have to use right. 363 23:39.960 --> 23:42.030 This is the same query we have to use. 364 23:42.030 --> 23:44.190 But what additional thing we have to do. 365 23:44.790 --> 23:48.240 Additional thing we have to do is where minimum two employee. 366 23:48.270 --> 23:51.030 So we want this three as a result. 367 23:52.080 --> 23:54.360 And this one as a result. 368 23:54.360 --> 23:57.780 We want four rows again from this grouping. 369 23:57.780 --> 24:01.050 So we have to use having having close right. 370 24:01.530 --> 24:03.840 We have to use having clause. 371 24:05.010 --> 24:07.470 So group by this one this one. 372 24:07.470 --> 24:12.720 And having greater than equal to two count of star greater than or equal to two. 373 24:12.750 --> 24:14.970 So it will give me four four records. 374 24:14.970 --> 24:15.570 Right. 375 24:15.870 --> 24:18.270 Whichever I have highlighted over here. 376 24:22.270 --> 24:24.640 Now, let's two minutes read this. 377 24:26.800 --> 24:28.090 Where versus having. 378 24:28.090 --> 24:30.940 So what is where and what is having. 379 24:31.150 --> 24:37.570 So if we want to filter based upon specific row right from entire table, if we want to do filter based 380 24:37.570 --> 24:42.250 upon specific row, then we are using the Where clause. 381 24:42.280 --> 24:43.150 Right. 382 24:43.420 --> 24:47.710 But select specific groups. 383 24:48.910 --> 24:55.570 If we want to do filter on specific group then we are using having clause condition apply before group 384 24:55.600 --> 24:56.050 by. 385 24:56.200 --> 25:02.680 So whenever we are using the group by, the condition will be applied before group by. 386 25:02.710 --> 25:10.630 See over here this where clause will be applied before group by and this having clause will be applied 387 25:10.630 --> 25:12.220 after group by right. 388 25:13.570 --> 25:16.780 So that is what it is written over here. 389 25:20.260 --> 25:22.540 Condition applied after group by. 390 25:23.020 --> 25:25.240 We can use aggregation function. 391 25:25.450 --> 25:26.830 Sorry we cannot. 392 25:26.860 --> 25:31.120 We can not use aggregation function in the Where clause. 393 25:32.770 --> 25:34.810 Can we write like this? 394 25:36.610 --> 25:37.450 Having. 395 25:41.830 --> 25:43.060 Count of star. 396 25:48.700 --> 25:51.340 An expression of non-boolean something. 397 25:51.340 --> 25:52.120 Something. 398 25:52.360 --> 25:54.820 Or count of job. 399 25:58.990 --> 26:03.880 We cannot use aggregation function in the where clause, right? 400 26:04.000 --> 26:05.290 We cannot use. 401 26:05.290 --> 26:08.230 But again the same thing. 402 26:08.980 --> 26:12.730 We can use aggregation function in the having clause. 403 26:12.790 --> 26:15.430 And grouping is not required for where condition. 404 26:15.430 --> 26:18.640 We can directly use the Where clause, right? 405 26:19.300 --> 26:24.400 If we want to data like this, then we can get the data. 406 26:24.430 --> 26:24.730 Okay. 407 26:24.760 --> 26:28.360 Over here we cannot because of this aggregate is used. 408 26:28.360 --> 26:31.570 So we need to remove this aggregate one over here. 409 26:33.250 --> 26:34.930 So this will give me data right. 410 26:34.960 --> 26:37.120 Grouping is not required over here. 411 26:37.300 --> 26:38.200 But. 412 26:43.150 --> 26:45.550 Yeah grouping is required to apply. 413 26:45.580 --> 26:53.980 Having condition whenever we want to filter rows on specific groups whenever we want to filter rows 414 26:54.010 --> 26:55.870 on some specific groups. 415 26:58.900 --> 27:02.140 So now I am unmuting you for two minutes. 416 27:03.730 --> 27:10.360 So now there is one very important concepts are there and that are rollup and cube. 417 27:11.080 --> 27:14.290 Role of n cube. 418 27:14.740 --> 27:21.320 So these are used to calculate subtotals totals and subtotals. 419 27:21.800 --> 27:24.290 So what is my requirement this time. 420 27:26.240 --> 27:30.860 This time my requirement is to display the department wise display. 421 27:34.460 --> 27:41.210 Display the department wise total salary as well as cumulative salary. 422 27:41.360 --> 27:42.770 So what is there? 423 27:52.100 --> 27:52.670 What? 424 27:56.960 --> 27:58.940 What is my requirement this time? 425 27:59.480 --> 28:02.000 This time I want department wise salary. 426 28:02.030 --> 28:02.600 Right. 427 28:02.780 --> 28:05.300 Department wise total salary. 428 28:09.050 --> 28:10.850 Total salary as well as. 429 28:10.850 --> 28:14.240 I want one extra column over here. 430 28:16.790 --> 28:17.690 Which is a. 431 28:19.700 --> 28:21.260 Total sum. 432 28:22.190 --> 28:26.090 Total sum of this particular. 433 28:26.090 --> 28:27.770 All these three departments. 434 28:29.180 --> 28:34.400 This three department total sum is 37,595. 435 28:34.430 --> 28:39.770 So this can be used in your reporting purposes many a times. 436 28:41.270 --> 28:43.160 So how can we get that. 437 28:43.400 --> 28:53.630 So for that purpose we need to write one additional keyword that is roll up roll off of department. 438 28:56.450 --> 28:59.510 So let me show you the result. 439 28:59.540 --> 29:04.760 So this is giving me the total departments by salary also. 440 29:04.760 --> 29:05.330 Right. 441 29:06.470 --> 29:09.710 This is giving me the same result what I have written over here. 442 29:09.710 --> 29:12.200 But over here it has mentioned null. 443 29:16.290 --> 29:18.570 So this is ideally not good. 444 29:18.900 --> 29:21.600 Not good for reading purposes, right? 445 29:21.630 --> 29:24.570 Ideally it should be all all. 446 29:24.570 --> 29:27.780 Ideally it should be grand total like this. 447 29:27.780 --> 29:28.440 Right. 448 29:29.310 --> 29:32.610 So how can I get that grand total over here. 449 29:36.090 --> 29:37.500 How can I get that. 450 29:37.500 --> 29:39.570 So let me write something. 451 29:41.790 --> 29:42.570 Is null. 452 29:48.090 --> 29:49.500 Of grand total. 453 29:52.530 --> 29:54.150 Now, will this query work? 454 29:54.180 --> 29:55.590 Let me see. 455 29:57.390 --> 30:00.120 Conversion failed when converting the vehicle value. 456 30:00.120 --> 30:02.460 Grand total to data type integer. 457 30:03.210 --> 30:13.470 Because of this, ten, 20 and 30 are there are there in the In Kasur and this grand total it is there 458 30:13.470 --> 30:16.410 in their care. 459 30:18.000 --> 30:20.850 So how can I resolve this error? 460 30:20.850 --> 30:22.350 I need to do cast right. 461 30:22.380 --> 30:24.480 We have seen cast and convert function. 462 30:24.720 --> 30:26.190 I need to do cast. 463 30:26.190 --> 30:26.820 Over here. 464 30:26.850 --> 30:27.630 Cast. 465 30:30.060 --> 30:32.310 As where? 466 30:32.310 --> 30:35.760 Care of ten or anything. 467 30:36.630 --> 30:37.950 Now let's see. 468 30:42.360 --> 30:44.790 It is giving me the perfect result, right? 469 30:47.010 --> 30:49.200 So this is the use of rollup. 470 30:51.330 --> 30:54.060 Now let's see how rollup will do. 471 31:00.360 --> 31:01.620 Now suppose my. 472 31:04.260 --> 31:12.490 Now suppose my requirement is I want department and job wise Grand total department and job wise grand 473 31:12.490 --> 31:13.210 total. 474 31:14.410 --> 31:20.170 Then I have to mention department and job, then some of salary from EMP. 475 31:20.200 --> 31:28.900 Then in the group by I have to mention role of and then in the role up close I have to mention two separate 476 31:28.900 --> 31:35.860 columns department as well as job right department as well as job. 477 31:37.810 --> 31:40.660 So let me show you how this will work. 478 31:54.280 --> 31:55.600 So ideally. 479 31:58.660 --> 32:04.990 Ideally this will work like it will do the salary of department and job wise. 480 32:06.190 --> 32:08.200 So let me. 481 32:09.910 --> 32:11.830 This is our original table, right? 482 32:21.760 --> 32:23.800 Now, Department number ten. 483 32:23.800 --> 32:25.720 Department number ten. 484 32:25.750 --> 32:28.210 Let me delete these two rows. 485 32:34.780 --> 32:35.530 Department number. 486 32:35.560 --> 32:37.390 What is the salary of clerk? 487 32:38.050 --> 32:40.390 So I have to go to clerk. 488 32:40.630 --> 32:41.410 Okay. 489 32:41.830 --> 32:42.910 2700. 490 32:42.940 --> 32:43.300 Right? 491 32:43.330 --> 32:45.190 This is 2700. 492 32:45.250 --> 32:47.230 So it is showing me over here. 493 32:47.230 --> 32:48.880 Department number ten. 494 32:49.150 --> 32:49.780 Clerk. 495 32:49.810 --> 32:52.750 Total salary, then Department number ten. 496 32:52.750 --> 32:53.320 Manager. 497 32:53.320 --> 32:57.400 Total salary is 2470. 498 32:57.430 --> 32:57.970 Right. 499 32:58.390 --> 32:59.920 It is written over here. 500 32:59.950 --> 33:01.660 2470. 501 33:01.990 --> 33:03.940 So that is showing me over here. 502 33:03.940 --> 33:05.260 2470. 503 33:05.440 --> 33:07.420 Then Department number 20. 504 33:07.450 --> 33:07.900 Analyst. 505 33:07.900 --> 33:11.080 Total salary for department number 20. 506 33:11.110 --> 33:11.620 Analyst. 507 33:11.650 --> 33:14.830 Total salary is one. 508 33:14.830 --> 33:16.090 Is this one? 509 33:19.240 --> 33:21.310 And department number 20. 510 33:21.340 --> 33:21.850 Analyst. 511 33:21.880 --> 33:23.770 Total salary where it is. 512 33:23.800 --> 33:24.310 Yeah. 513 33:24.670 --> 33:26.350 One is this one. 514 33:27.040 --> 33:29.020 So 3003 thousand. 515 33:29.020 --> 33:29.950 6000. 516 33:29.980 --> 33:30.580 Right. 517 33:31.090 --> 33:33.430 So that is what mentioned over here. 518 33:36.280 --> 33:37.180 6000. 519 33:37.630 --> 33:47.140 So what it will do, it will give me it will give me the salary of department and job wise. 520 33:48.100 --> 33:49.900 Department and job wise. 521 33:50.020 --> 33:52.750 Then it will give me the salary of only. 522 33:54.400 --> 33:56.230 Only department wise. 523 33:57.400 --> 33:58.840 Only department wise. 524 33:58.840 --> 34:01.630 So C10150. 525 34:01.660 --> 34:05.510 What is mentioned over here 10150. 526 34:05.540 --> 34:07.400 Right now again. 527 34:07.400 --> 34:08.570 Department number 20. 528 34:08.600 --> 34:09.110 Clerk. 529 34:09.110 --> 34:10.340 Department number 20. 530 34:10.370 --> 34:11.090 Manager. 531 34:11.360 --> 34:11.930 And then. 532 34:11.930 --> 34:12.740 Department number. 533 34:12.770 --> 34:13.610 Total salary. 534 34:13.640 --> 34:15.440 17450. 535 34:16.100 --> 34:17.330 What is written over here? 536 34:17.330 --> 34:19.070 17450. 537 34:19.670 --> 34:21.380 Then again, Department number 30. 538 34:21.410 --> 34:22.010 Clerk. 539 34:22.730 --> 34:24.290 Department number 30. 540 34:24.350 --> 34:25.250 Manager. 541 34:25.280 --> 34:26.810 Department number 30. 542 34:27.590 --> 34:28.430 Job wise. 543 34:28.670 --> 34:29.540 And at the end. 544 34:29.570 --> 34:30.770 Department number 30. 545 34:30.800 --> 34:32.690 Total salary it will give me. 546 34:33.110 --> 34:36.290 So what is the total salary for Department number 30? 547 34:38.570 --> 34:40.670 9970. 548 34:40.760 --> 34:42.950 And at the last what it will give. 549 34:43.040 --> 34:43.970 It will give me. 550 34:44.000 --> 34:45.170 Grand total. 551 34:46.250 --> 34:49.520 Which, which is it has given me over here as well. 552 34:50.360 --> 34:51.530 Grand total. 553 34:52.670 --> 34:54.110 So null. 554 34:54.110 --> 34:54.710 Null. 555 34:54.920 --> 34:56.510 And it will give me grand total. 556 34:56.540 --> 34:58.520 So how this query will executed. 557 34:58.520 --> 34:59.330 Let's see. 558 35:01.670 --> 35:02.360 Over here. 559 35:02.360 --> 35:03.800 Wherever null are there. 560 35:03.800 --> 35:08.150 I have mentioned converted to const of worker. 561 35:08.180 --> 35:09.560 Caste of worker. 562 35:10.880 --> 35:15.080 Because of null is coming in this department column as well as job column. 563 35:15.290 --> 35:19.730 So in the department column also I have done the caste and in the job column. 564 35:19.730 --> 35:24.260 Also I have done the caste and group by department and job. 565 35:24.260 --> 35:25.760 Both I have done right. 566 35:28.910 --> 35:32.600 Now let's see one more concept very quickly that is cube. 567 35:33.950 --> 35:44.960 So cube will be little more more expansion of this one, little more expansion of roll up, little more 568 35:44.990 --> 35:46.400 expansion of roll up. 569 35:47.060 --> 35:57.740 So let's see how what we have done over here, same query we have written, but now we have done then 570 35:57.740 --> 35:59.090 group by cube. 571 35:59.750 --> 36:02.400 So let's compare these two results. 572 36:05.550 --> 36:08.910 You will obviously get to know very well. 573 36:11.400 --> 36:16.470 This is the result set of cube and below one it has written roll up. 574 36:18.000 --> 36:20.190 This is the result set of roll up. 575 36:27.330 --> 36:32.160 Now over here it is showing me only department wise department wise total. 576 36:32.190 --> 36:32.760 Right. 577 36:33.300 --> 36:36.750 It is showing me over here only department wise total. 578 36:36.750 --> 36:42.930 But over here it is showing me job wise also. 579 36:47.760 --> 36:52.950 So how many total analyst we can say how many total analysts are there. 580 36:53.160 --> 36:57.810 There are total two total analysts are there and both are working in the department. 581 36:57.840 --> 36:59.130 Number 20 right. 582 37:00.540 --> 37:02.190 which we have seen over here. 583 37:02.220 --> 37:06.600 There are total two analysts are there and both are working in department number 20. 584 37:07.230 --> 37:09.930 So it will give me analyst wise salary. 585 37:09.930 --> 37:13.500 Also cube will give me analyst wise salary. 586 37:13.500 --> 37:16.350 Then how many total number of clerks are there. 587 37:18.360 --> 37:26.100 So we can say department number ten clerk then department number 20 clerk. 588 37:27.180 --> 37:32.220 So whatever the total number of clerks are there, it will give me the salary of this one also. 589 37:34.320 --> 37:42.000 This is 71507150 right now. 590 37:42.000 --> 37:46.530 This will give me uh, again job wise precedent. 591 37:46.530 --> 37:47.370 Precedent. 592 37:47.370 --> 37:49.650 So again job wise. 593 37:50.160 --> 37:59.890 So in short, we can say cube is more expansion of the cube is more expansion of the roll up. 594 38:01.960 --> 38:03.670 This is the result of roll up. 595 38:03.820 --> 38:05.860 And this is the result of. 596 38:08.050 --> 38:08.500 Cube. 597 38:08.500 --> 38:09.190 Right. 598 38:11.470 --> 38:15.130 So now let's come over here. 599 38:19.540 --> 38:21.940 To display department wise maximum salary. 600 38:21.940 --> 38:25.180 So it will give me the maximum salary of each department. 601 38:25.900 --> 38:34.240 And this one this is also very simple one department wise maximum salary. 602 38:34.240 --> 38:36.820 But I want employee information as well. 603 38:36.910 --> 38:44.620 So I have what I have done where salary in select max of salary from employee group by department. 604 38:45.040 --> 38:47.830 So this will give me name also as well. 605 38:50.740 --> 38:53.260 Now I will unmute you all for ten minutes. 606 38:53.260 --> 38:55.180 You can ask your question.