1 00:00:00,090 --> 00:00:00,780 In this video. 2 00:00:00,780 --> 00:00:05,700 We won't learn anything new, but I want to show that we can combine a join like inner join, which 3 00:00:05,700 --> 00:00:13,080 is all we know so far, aka the default join type with things like group B, we can get more complicated. 4 00:00:13,590 --> 00:00:17,940 So what I'm going to do is recreate my table. 5 00:00:17,940 --> 00:00:21,630 So I'm going to drop the tables and then I'm going to reinsert these values. 6 00:00:21,630 --> 00:00:22,770 You don't have to do that. 7 00:00:22,770 --> 00:00:26,430 The only reason I'm going to do that is because I think I made some alterations. 8 00:00:26,430 --> 00:00:32,729 I added in an order or two, and I don't want to have different results from you, so I'm just going 9 00:00:32,729 --> 00:00:33,570 to copy this code. 10 00:00:33,570 --> 00:00:42,480 You can have a copy of it as well if you want to, and I'll drop my table orders and drop table customers. 11 00:00:43,350 --> 00:00:43,710 All right. 12 00:00:43,710 --> 00:00:45,540 Recreate all of that at once. 13 00:00:45,840 --> 00:00:48,750 Select start from orders just to make sure it works. 14 00:00:48,960 --> 00:00:51,060 Okay, So let's rejoin our data. 15 00:00:51,060 --> 00:00:54,900 Let's get a little more practice with that select star. 16 00:00:54,900 --> 00:01:01,500 Or maybe we'll just do first name, Last name and I think it's amount. 17 00:01:01,500 --> 00:01:02,730 Or is it order amount? 18 00:01:02,880 --> 00:01:08,250 It's order, date and amount from customers. 19 00:01:08,250 --> 00:01:10,080 Of course, those properties don't exist. 20 00:01:10,080 --> 00:01:12,180 At least these two don't exist in customers. 21 00:01:12,180 --> 00:01:14,400 We then need to do a join. 22 00:01:15,160 --> 00:01:21,280 And or do a join orders table on and then the order here doesn't matter. 23 00:01:21,280 --> 00:01:26,020 But I'll say orders dot customer ID is equal to customers. 24 00:01:26,020 --> 00:01:27,610 Date ID nothing new there. 25 00:01:27,610 --> 00:01:30,160 That's a recap of what we saw in the previous video. 26 00:01:30,160 --> 00:01:37,270 So now I have a list essentially of every order and the corresponding user who made the order or customer 27 00:01:37,270 --> 00:01:38,140 who made the order. 28 00:01:38,560 --> 00:01:42,400 Why don't I try and find the total amount that each customer spent? 29 00:01:42,610 --> 00:01:48,280 So I need to group by first name and last name for a unique customer. 30 00:01:48,430 --> 00:01:53,020 Because of course, we could have multiple people at the same last name and same first name, or rather 31 00:01:53,020 --> 00:01:55,750 same last name or the same first name. 32 00:01:55,750 --> 00:01:59,440 So if we instead grouped by both of them together, that should work for us. 33 00:01:59,440 --> 00:02:04,570 We'll get these groups that have these different amounts and then we'll sum up the amounts. 34 00:02:04,780 --> 00:02:07,300 And I'm going to do this back here. 35 00:02:07,300 --> 00:02:11,410 So we have a record of it in SQL Workbench or just any text editor. 36 00:02:11,410 --> 00:02:12,910 It's also easier to edit. 37 00:02:12,910 --> 00:02:17,620 Just got to be careful because when I copy and paste from the shell, I get that arrow. 38 00:02:18,530 --> 00:02:26,730 Okay, so instead of just doing this and stopping there, I'm going to say group buy and I want to group 39 00:02:26,730 --> 00:02:28,540 by first name. 40 00:02:29,480 --> 00:02:31,360 And last name. 41 00:02:31,370 --> 00:02:39,020 Now, technically, what I could do is write customer dot, first name customers that last name, but 42 00:02:39,020 --> 00:02:45,240 I don't need to because there's no first name in the orders table or last name in the orders table. 43 00:02:45,260 --> 00:02:50,870 But if I were trying to use ID here, well, my SQL doesn't know which ID I'm referring to, which is 44 00:02:50,870 --> 00:02:58,520 why we have to say customer's ID Anyway, I'm going to do my group by and then instead of selecting 45 00:02:58,520 --> 00:03:01,070 first name, last name order date in amount. 46 00:03:01,070 --> 00:03:06,830 Well, order date isn't really going to work anymore because it's no longer in common between all the 47 00:03:06,830 --> 00:03:07,130 roads. 48 00:03:07,130 --> 00:03:10,700 I'm grouping the rows by first name and last name. 49 00:03:10,760 --> 00:03:16,850 So instead of order date, I'm going to get rid of that and instead of amount I'm going to some amount 50 00:03:16,970 --> 00:03:18,410 just like that. 51 00:03:18,890 --> 00:03:20,690 And let's see how that works. 52 00:03:21,410 --> 00:03:23,090 Semicolon, of course, is needed. 53 00:03:23,720 --> 00:03:30,590 So we see our three different customers Boy George, George Michael, Bette Davis and the sum of all 54 00:03:30,590 --> 00:03:31,190 of their orders. 55 00:03:31,190 --> 00:03:34,130 And we only have five orders total, so pretty small. 56 00:03:34,130 --> 00:03:37,460 But if we had hundreds or thousands, it would work just the same. 57 00:03:37,820 --> 00:03:39,580 So just check the math here. 58 00:03:39,590 --> 00:03:40,880 Bette Davis for 50. 59 00:03:40,880 --> 00:03:45,440 Well, she only has one order, but George Michael 813 That makes sense. 60 00:03:45,440 --> 00:03:51,650 867 plus 1250 Boy George one 3599 plus 35 All right. 61 00:03:51,650 --> 00:03:54,320 We could even throw in an order by at the end there. 62 00:03:54,500 --> 00:03:56,810 Let's do order by the amount. 63 00:03:56,810 --> 00:04:03,020 So why don't we call this as total and then order by total? 64 00:04:04,070 --> 00:04:07,640 And now we get our highest spenders. 65 00:04:07,640 --> 00:04:10,700 Well, our lowest spenders up to our highest spenders. 66 00:04:10,730 --> 00:04:13,340 Of course, we could do descending to if we wanted. 67 00:04:13,970 --> 00:04:19,790 So I just wanted to show an example of doing a group buy with a join super common. 68 00:04:20,180 --> 00:04:25,130 The join just helps us get some information side by side, but it doesn't really tell us or give us 69 00:04:25,130 --> 00:04:30,500 answers or show us anything particularly useful until we then do some analysis. 70 00:04:30,500 --> 00:04:36,410 And a common way of analyzing any of our data is using group buy and then some aggregation function. 71 00:04:36,410 --> 00:04:40,300 So of course you could find the average, we could find all sorts of things. 72 00:04:40,320 --> 00:04:46,130 You could find the earliest date, the latest date we could use Min and Max, but I think some is just 73 00:04:46,130 --> 00:04:47,630 a simple example to start with. 74 00:04:47,810 --> 00:04:52,880 Next up, we're going to move on to a different type of join, which is the left join.