In this video.
We won't learn anything new, but I want to show that we can combine a join like inner join, which
is all we know so far, aka the default join type with things like group B, we can get more complicated.
So what I'm going to do is recreate my table.
So I'm going to drop the tables and then I'm going to reinsert these values.
You don't have to do that.
The only reason I'm going to do that is because I think I made some alterations.
I added in an order or two, and I don't want to have different results from you, so I'm just going
to copy this code.
You can have a copy of it as well if you want to, and I'll drop my table orders and drop table customers.
All right.
Recreate all of that at once.
Select start from orders just to make sure it works.
Okay, So let's rejoin our data.
Let's get a little more practice with that select star.
Or maybe we'll just do first name, Last name and I think it's amount.
Or is it order amount?
It's order, date and amount from customers.
Of course, those properties don't exist.
At least these two don't exist in customers.
We then need to do a join.
And or do a join orders table on and then the order here doesn't matter.
But I'll say orders dot customer ID is equal to customers.
Date ID nothing new there.
That's a recap of what we saw in the previous video.
So now I have a list essentially of every order and the corresponding user who made the order or customer
who made the order.
Why don't I try and find the total amount that each customer spent?
So I need to group by first name and last name for a unique customer.
Because of course, we could have multiple people at the same last name and same first name, or rather
same last name or the same first name.
So if we instead grouped by both of them together, that should work for us.
We'll get these groups that have these different amounts and then we'll sum up the amounts.
And I'm going to do this back here.
So we have a record of it in SQL Workbench or just any text editor.
It's also easier to edit.
Just got to be careful because when I copy and paste from the shell, I get that arrow.
Okay, so instead of just doing this and stopping there, I'm going to say group buy and I want to group
by first name.
And last name.
Now, technically, what I could do is write customer dot, first name customers that last name, but
I don't need to because there's no first name in the orders table or last name in the orders table.
But if I were trying to use ID here, well, my SQL doesn't know which ID I'm referring to, which is
why we have to say customer's ID Anyway, I'm going to do my group by and then instead of selecting
first name, last name order date in amount.
Well, order date isn't really going to work anymore because it's no longer in common between all the
roads.
I'm grouping the rows by first name and last name.
So instead of order date, I'm going to get rid of that and instead of amount I'm going to some amount
just like that.
And let's see how that works.
Semicolon, of course, is needed.
So we see our three different customers Boy George, George Michael, Bette Davis and the sum of all
of their orders.
And we only have five orders total, so pretty small.
But if we had hundreds or thousands, it would work just the same.
So just check the math here.
Bette Davis for 50.
Well, she only has one order, but George Michael 813 That makes sense.
867 plus 1250 Boy George one 3599 plus 35 All right.
We could even throw in an order by at the end there.
Let's do order by the amount.
So why don't we call this as total and then order by total?
And now we get our highest spenders.
Well, our lowest spenders up to our highest spenders.
Of course, we could do descending to if we wanted.
So I just wanted to show an example of doing a group buy with a join super common.
The join just helps us get some information side by side, but it doesn't really tell us or give us
answers or show us anything particularly useful until we then do some analysis.
And a common way of analyzing any of our data is using group buy and then some aggregation function.
So of course you could find the average, we could find all sorts of things.
You could find the earliest date, the latest date we could use Min and Max, but I think some is just
a simple example to start with.
Next up, we're going to move on to a different type of join, which is the left join.