stores, but it looks like I also need information about the payment. So notice here in our payments table, each payment is associated with a single rental. So let's join in that data. Join the payments table on the condition that the payment's rental_id matches up with this current rental_id. Okay, so now you can see for this rental, right over here, this is the amount we received. But next, it's still a little blurry because I don't know what the name of the rental was. I just have an ID. So let's figure out, if I'm starting from the rentals table, how can I figure out the name of the film?

So let's figure out, if I'm starting from the rentals table, how can I figure out the name of the film? Let's see. The film doesn't have anything there, but we do know that a rental is associated with an inventory ID, and the inventory ID is associated with the film. So we'll have to go through that table to fetch it, just like we did in the last episode. So I'm going to join the inventory table on the condition that the inventory's ID is again equal to the rental's inventory ID. Okay, so now let's run that, and we have a little more data. So I have the rental information, the payment information, and then also the inventory information.

Okay, but now I still want to see the most profitable records, and if I run it, they're not in any particular order. So let's order by the sales in descending order. So the most profitable ones first. All right, so this is looking good, but one other thing I might like to know is the total number of times each movie was rented. Easy enough. We'll use count. So, you know, we could count a specific column here, but it's the same as just saying, give me the number of records for each grouped item, and we'll call that rentals.

So, you know, we could count a specific column here, but it's the same as just saying, give me the number of records for each grouped item, and we'll call that rentals. And there we go. So TelegraphVoyage has made $231, and it was rented 27 times. So now you're feeling pretty good, but your boss says, there's too much data here. Just let me know which movies have made at least $200 or more. Okay, so you think, well, that's easy. I can use the WHERE clause, right? And sure enough, you try it. WHERE the sales is greater than $200, and then you run it, and it fails again.

And sure enough, you try it. WHERE the sales is greater than $200, and then you run it, and it fails again. Okay, this is a really important thing to understand. When you're using WHERE, you must always reference an actual column that you're selecting from. So in our case, we used sales, but sales is simply an alias for some amount. It's not a column, so it doesn't work. And the same would be true if I tried to filter based on the rentals. Rentals is not a column. It is the count. It's the aggregate.

The information I want is the total rentals that have sales greater than $200. And if we run it, here we go. Now I can see we have about six different entries that have made $200 or more.