title: SQL how to count total rows for each customer with group by?
body: <p>I'm struggling with a question that said "which city of customers watches the most number of movies? (based on the number of films rented)".</p>
  
  <p>I am doing this using the Seikila sample database in MySQL. The city entity has no relationship with rental and customer entity, but there's a address entity contain address_id which is id-dependent to customer entity. Then, the address entity has an attribute called city_id which the city entity also have got. And the customer_id attribute linked both customer and rental entities. </p>
  
  <p>Also, I have to calculate the total number of films each customer in each city have rented.</p>
  
  <p>I have only done the join part (code updated):</p>
  
  <pre><code>select rental_id,cust.customer_id,city.city_id,city.city from rental as r  
  inner join customer as cust on r.customer_id=cust.customer_id inner join
  address as a on cust.address_id=a.address_id inner join city as city on
  city.city_id=a.city_id 
  </code></pre>
  
  <p>I realized that I should add city_id to the table, so my question will be:</p>
  
  <p>how to count the number of rows of an unique customer_id so that I can get <strong>the total numbers of film rented</strong> of <strong>in each city</strong>, and with a group by?</p>