1 00:00.720 --> 00:01.760 Hello everyone. 2 00:02.360 --> 00:03.280 Good morning all. 3 00:04.120 --> 00:10.320 So today we will see another SQL query asked in product based company. 4 00:11.160 --> 00:14.040 So let's directly jump to the question. 5 00:14.720 --> 00:22.200 Assume you are given the table on user viewership categorized by device type, where the three types 6 00:22.200 --> 00:25.160 are laptop, tablet, and phone. 7 00:26.840 --> 00:32.960 Write a query that calculates the total viewership for laptops and mobile devices, where mobile is 8 00:32.960 --> 00:37.400 defined as the sum of tablet and phone viewership. 9 00:39.120 --> 00:46.760 Or put the total viewership for laptop as laptop reviews and total viewership for mobile devices as 10 00:47.280 --> 00:48.680 mobile use. 11 00:49.240 --> 00:49.760 Right? 12 00:50.600 --> 00:52.880 So here we need to. 13 00:55.280 --> 00:59.240 Laptop use right now we have given the table viewership table. 14 01:00.520 --> 01:02.480 So let me create the table. 15 01:06.720 --> 01:12.640 So in that user ID device type and you time is given right. 16 01:13.020 --> 01:15.700 So let me insert the data into the same. 17 01:21.860 --> 01:22.140 Okay. 18 01:22.140 --> 01:25.060 Now let's see the table. 19 01:25.700 --> 01:32.900 So uh, we have given the user ID then device type and view time is given. 20 01:32.900 --> 01:33.380 Right. 21 01:33.620 --> 01:41.580 Now we need to count the use on laptop and on mobile. 22 01:41.820 --> 01:46.580 So in mobile we are considering tablet as well as phone right. 23 01:47.020 --> 01:52.100 So in you can say that tablet is for one, phone is for one. 24 01:52.100 --> 01:54.740 And again tablet is for one right. 25 01:55.260 --> 02:01.300 So we can say that total three users are there in phone right. 26 02:01.340 --> 02:03.780 And laptop users are two right. 27 02:04.420 --> 02:07.300 So now we need to count these users right. 28 02:07.940 --> 02:17.540 So select count of user ID as mobile use from users table where lower of device type in tablet and phone. 29 02:17.580 --> 02:23.140 So this both are we can consider as a mobile viewership right. 30 02:23.180 --> 02:24.260 As mobile use. 31 02:24.580 --> 02:26.360 So let me select the data. 32 02:27.360 --> 02:29.040 So three are there right. 33 02:29.040 --> 02:30.800 As we have seen three are there. 34 02:31.400 --> 02:33.760 Then for laptop it is two are there right. 35 02:34.120 --> 02:40.840 So again we are counting the user ID where device type in laptop. 36 02:41.440 --> 02:44.760 So let me execute the query. 37 02:44.760 --> 02:46.800 So it is given two right. 38 02:47.560 --> 02:49.960 Now how can we combine this. 39 02:49.960 --> 02:53.720 Both the result in two separate columns. 40 02:54.320 --> 02:54.840 Right. 41 02:54.880 --> 02:56.000 We need to give. 42 02:58.640 --> 03:03.840 Like in one column laptop use and in another column mobile use. 43 03:03.880 --> 03:04.320 Right. 44 03:04.720 --> 03:08.640 So for that purpose let me again copy this. 45 03:08.640 --> 03:09.600 Both the queries. 46 03:10.440 --> 03:13.160 Now how can we combine. 47 03:13.160 --> 03:16.960 So for that purpose we need to create a city with. 48 03:19.080 --> 03:20.080 City's. 49 03:22.880 --> 03:25.040 So this will become our first city. 50 03:25.720 --> 03:28.400 Then how can we create the another city. 51 03:28.440 --> 03:33.800 So simple comma city two s. 52 03:35.520 --> 03:38.850 So this is a perfect example of multiple cities. 53 03:39.330 --> 03:42.450 Now how can we combine the result set of both? 54 03:42.770 --> 03:45.970 So city comma, city two. 55 03:46.810 --> 03:49.610 Actually we do not have any joining column over here. 56 03:49.610 --> 03:50.010 Right. 57 03:50.890 --> 03:52.850 So that we can give like this. 58 03:53.410 --> 03:55.810 Now let me execute the query. 59 03:59.050 --> 04:01.610 So it is giving three comma two right. 60 04:02.730 --> 04:05.650 That is our requirement right. 61 04:07.570 --> 04:10.690 Write a query that calculates the total viewership for laptop. 62 04:11.130 --> 04:17.570 So two will be the total viewership of laptop and mobile devices where mobile is defined as the sum 63 04:17.570 --> 04:18.930 of tablet and phone. 64 04:19.530 --> 04:24.050 So this is for mobile devices right. 65 04:24.970 --> 04:27.130 Which is the addition of tablet and phone. 66 04:27.130 --> 04:27.650 Both. 67 04:30.130 --> 04:30.530 Output. 68 04:30.530 --> 04:36.810 The total viewership for laptop is laptop use and total viewership for mobile devices as mobile use. 69 04:37.170 --> 04:37.650 Right. 70 04:38.570 --> 04:40.530 So this will be the result. 71 04:42.690 --> 04:48.610 Please, uh, if you like this video, please like and, uh, comment and subscribe to my channel. 72 04:49.650 --> 04:50.410 Thank you.