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.

