An excellent way to learn MySQL is to think to yourself, if this were my domain, what sort of questions might I ask? For example, yes, we have the customers table, right? Well maybe I'd like to see this. Show me all of my customers, as well as the total number of rentals they currently have checked out. That seems reasonable. Okay, so let's hunt around. We do have this rentals table. So this shows us, for example, that the customer with an ID of 459 currently has a rental with

We do have this rentals table. So this shows us, for example, that the customer with an ID of 459 currently has a rental with an ID of 2. And the key thing to remember is, any one customer can have multiple rentals. Alright, let's play around with the query. Yes, I want my customers, so I will run that. But we are returning a lot of data that I don't really care about. So instead, let's be explicit. I want to pull in the customer's ID, their first name, and their last name, and let's leave it like that.

I want to pull in the customer's ID, their firstName, and their lastName, and let's leave it like that. Okay, so now we get this. But next, it sounds like we need to pull in information about the rentals table. So here's what you might think. You might say, alright, well, I guess I need the rentalID? Something like that? But if I run it, it's going to let you know, hey, you're working with the customers table, but you're trying to fetch information about the rentals table. You can't do that.

but you're trying to fetch information about the rentals table. You can't do that. So let's join it in. And we're going to use a let's join, once again, because I want to favor the left side of the join keyword. So no matter what, I want all of my customers. Even if they don't have rentals, I still want the customer returned. So let's join the rentals table on the condition that the rentals.customer_id column matches up with the id of the customer. That is the connecting tissue.

So this isn't exactly what I want. I still want a single record for Mary Smith, but then I want a column that's maybe named Total Rentals Checked Out or something like that. So how would we do that? Well, it sounds like I want to group all of these results. I want to smoosh them or group them down to one record. So this is where we can begin learning about aggregates. So aggregate is kind of a scary word, but it means exactly what the definition says. A whole formed by combining several elements. Alright, so in the context of MySQL, it's a number we are generating based on all of

So think, right now we're trying to fetch a list of all the customers, and then we're adding this aggregate here. But we haven't declared how we're going to group them. Let's group them according to each customer. And there we go. Now this is what we want. But notice here, the column name has the full function signature, not what you want. Let's give it an alias. This is Rentals Checked Out. There we go.