select customer_id, count(*)
from rental
group by customer_id
order by 2 desc;
If you want to input conditions, you cannot use where clause due to the fact that the where clause come after the from clause always. So the grouping happens after the where clause, results in errors.
Instead of where, you will use having:
select customer_id, count(*)
from rental
group by customer_id
having count(*) >= 40;
max, min, count, avg, sum
select customer_id,
max(amount) max_amt, min(amount) min_amt,
avg(amount) avg_amt, sum(amount) sum_amt,
count(*) num_payments
from payment
group by customer_id;
With group by clause, the server knows to group together rows with the same customer_id and perform aggregate functions to each 599 customers.
select count(customer_id),
count(distinct customer_id)
from payment;
Not only that you can pass columns as arguments into aggregate functions, you can pass in expressions as well.
select max(
datediff(return_date, rental_date)
) from rental;
The whole query using this can be complex, but it will work as long as the expressions return a number, string or date.