From customer limit 5; customer id

Exercise
Count the number of cities for each country id in the city table. Order the results by count(*).
HAVING
HAVING is similar to a WHERE clause but it applies to the result of a GROUP BY operation; WHERE applies before data are grouped by GROUP BY;
We can get total amount spent by each customer with:
SELECT customer_id, sum(amount) FROM payment
GROUP BY customer_id;
But how do we just get the customers that spent more than $200?
SELECT customer_id, sum(amount) FROM payment
GROUP BY customer_id
HAVING sum(amount) > 200;
Exercise
Select the country_ids from the city table that have more than 20 cities associated with them (country_ids that appear more than 20 times).
Dates
So far, we’ve selected numeric values and string values. There are also other types, with one of the most common of those being dates. Dates are in the format YYYY-MM-DD.
SELECT count(*)
FROM customer
WHERE create_date = '2006-02-14';
Timestamps are dates with a time (and possibly timezone) also attached.
SELECT rental_date
FROM rental
WHERE rental_date < '2005-05-25';
dvdrental=# SELECT rental_date FROM rental WHERE rental_date < '2005-05-25';
rental_date
---------------------
2005-05-24 22:53:30
2005-05-24 22:54:33
2005-05-24 23:03:39
2005-05-24 23:04:41
2005-05-24 23:05:21
2005-05-24 23:08:07
2005-05-24 23:11:53
2005-05-24 23:31:46
(8 rows)
This will get you everything before 2005-05-25 00:00:00.
Note that you want to use > or < with timestamps, because date equality for just the date part doesn’t work:
SELECT rental_date
FROM rental
WHERE rental_date = '2005-05-24';