1 00:00:00,120 --> 00:00:04,920 One more quick thing we need to cover with Group B is grouping by multiple columns. 2 00:00:05,220 --> 00:00:11,670 So with our authors or books, data table data set, we know that there's authors that have the same 3 00:00:11,670 --> 00:00:13,860 last name but not the same first name. 4 00:00:13,860 --> 00:00:15,540 I think it's just two authors. 5 00:00:15,990 --> 00:00:17,430 Let's do author. 6 00:00:17,850 --> 00:00:18,840 F name. 7 00:00:19,200 --> 00:00:22,200 Author L name from books. 8 00:00:22,200 --> 00:00:25,680 And then let's order by author. 9 00:00:25,710 --> 00:00:27,390 L Name by the last name. 10 00:00:27,870 --> 00:00:32,490 So here we can see Carver, Eggers, Foster Wallace. 11 00:00:32,490 --> 00:00:33,780 And then we get to Harris. 12 00:00:33,990 --> 00:00:38,100 We have Harris, Dann and Harris Freedom. 13 00:00:38,520 --> 00:00:43,950 And when I do a group by by the last name, which is what I did earlier, and let's say I counted up 14 00:00:43,950 --> 00:00:47,310 the number of books that each wrote, which we've done already. 15 00:00:47,310 --> 00:00:58,410 Select Author L Name comma Count Star from Books Group by the last name. 16 00:00:59,010 --> 00:01:06,870 I did this, and it was technically true that Harris has two books, but this doesn't really tell the 17 00:01:06,870 --> 00:01:08,990 picture that there's two authors there. 18 00:01:09,000 --> 00:01:11,130 We just know it's one last name. 19 00:01:11,130 --> 00:01:17,700 Harris So I probably want to write a query that actually groups by the last name and the first name, 20 00:01:17,850 --> 00:01:22,890 what might be better is to group by the full name, which doesn't exist, and maybe we should store 21 00:01:22,890 --> 00:01:23,490 it that way. 22 00:01:23,490 --> 00:01:24,780 But we didn't. 23 00:01:24,780 --> 00:01:25,740 Or I didn't. 24 00:01:26,250 --> 00:01:34,710 So instead we can add on a second part to the group by after author l name will do author F name for 25 00:01:34,710 --> 00:01:35,400 first name. 26 00:01:36,480 --> 00:01:37,860 And if I do it that way. 27 00:01:37,860 --> 00:01:40,590 Well, now we just have Harris showing up twice. 28 00:01:40,590 --> 00:01:42,330 Why is Harris there twice? 29 00:01:42,330 --> 00:01:44,250 Because there's two different groups. 30 00:01:44,250 --> 00:01:49,440 One is based off of Harris and then Frieda and the other is Harris. 31 00:01:49,440 --> 00:01:51,420 And then Dan, I think it's Dan. 32 00:01:51,960 --> 00:01:57,810 I'm only displaying author L name, but I could add in author F name if I wanted to. 33 00:01:57,810 --> 00:01:59,790 Let's just put this over here so we have it. 34 00:02:00,090 --> 00:02:07,350 I'll reformat it slightly and say that we want the first name, author F name, and then the last name 35 00:02:07,350 --> 00:02:10,380 and then the count for each one of those groups. 36 00:02:10,530 --> 00:02:15,900 Now, most of those groups are going to be the same first name and last name, so we won't notice a 37 00:02:15,900 --> 00:02:16,350 difference. 38 00:02:16,350 --> 00:02:18,660 Like there's only one Jhumpa Lahiri. 39 00:02:18,660 --> 00:02:23,610 But in our case, there are two different Harris's Dan and Freedom. 40 00:02:23,610 --> 00:02:24,270 So there we go. 41 00:02:24,270 --> 00:02:26,970 We get our counts for each one of those groups. 42 00:02:27,090 --> 00:02:28,200 Jhumpa Lahiri two. 43 00:02:28,200 --> 00:02:29,340 Neil Gaiman three. 44 00:02:29,340 --> 00:02:30,630 Dave Eggers three. 45 00:02:30,630 --> 00:02:35,400 But once we come down here, we get a different result because there's two different Harris's. 46 00:02:35,700 --> 00:02:38,460 So that's important to know that you can do it that way. 47 00:02:38,880 --> 00:02:46,320 We honestly could even group by just one thing if we concatenated author first name and last name to 48 00:02:46,320 --> 00:02:49,620 be a full name like this select Can cat. 49 00:02:50,010 --> 00:02:59,130 We've seen this before F name and then maybe a space and then author L name from books. 50 00:02:59,820 --> 00:03:02,250 Let's make sure that that works first. 51 00:03:02,250 --> 00:03:03,810 There's no grouping whatsoever. 52 00:03:03,840 --> 00:03:05,160 Here's the full names. 53 00:03:05,400 --> 00:03:12,210 Let's rename that give it an alias as author from books. 54 00:03:12,480 --> 00:03:20,310 And then what I can do is group by that exact property or that exact thing I just created here called 55 00:03:20,310 --> 00:03:22,680 author grouped by authors. 56 00:03:22,710 --> 00:03:26,640 It's not a column that exists in the table, but I can still group by it. 57 00:03:27,030 --> 00:03:34,230 And then instead of just selecting the concatenation, I'm also going to select after this whole field 58 00:03:34,230 --> 00:03:38,190 here called Author, I'm going to select Count Star. 59 00:03:38,430 --> 00:03:43,860 So how many rows exist in each one of these groups that are created by Author? 60 00:03:44,130 --> 00:03:47,190 So the order of all this seems weird, but it will work. 61 00:03:47,190 --> 00:03:51,060 If I didn't mess up any any syntax I pasted in. 62 00:03:51,060 --> 00:03:51,960 And there we go. 63 00:03:51,960 --> 00:03:56,160 We see Jhumpa Lahiri two, Neil Gaiman three. 64 00:03:56,520 --> 00:04:03,150 So we get the same results, the same counts that we did when we grouped by the first name or the last 65 00:04:03,150 --> 00:04:05,340 name and then first name separately. 66 00:04:05,490 --> 00:04:07,800 But the mechanic of this is slightly different. 67 00:04:07,830 --> 00:04:14,250 We're only grouping by one property or one column, but that value happens to be the result of concatenating 68 00:04:14,250 --> 00:04:16,829 first name with a space and then last name. 69 00:04:16,829 --> 00:04:22,620 And then we count up how many occurrences or how many rows have that as their author. 70 00:04:23,040 --> 00:04:25,140 And for a lot of them it's just one. 71 00:04:25,140 --> 00:04:31,080 But for some of them, like Dave Eggers, that full name occurs three times when it's formed for each 72 00:04:31,080 --> 00:04:32,040 one of those rows. 73 00:04:33,030 --> 00:04:38,190 So that's grouping by multiple values, even though at the end of the day, this example is just one 74 00:04:38,190 --> 00:04:38,730 value. 75 00:04:38,730 --> 00:04:42,570 This one here we're grouping by two different columns and you can keep going. 76 00:04:43,320 --> 00:04:47,400 So it's not totally uncommon to have multiple pieces to a group by.