SQL: GROUP BY Statements - HAVING

---

SQL: GROUP BY Statements

HAVING

The HAVING clause sets the condition for group rows created by the GROUP BY clause after the GROUP BY clause applies while the WHERE clause sets the condition for individual rows before GROUP BY clause applies.

Conditions:

Operator	Description
\(=\)	Equal
\(>\)	Greater than
\(<\)	Less than
\(\geq\)	Greater than or equal to
\(\leq\)	Less than or equal to
\(\neq\)	Not equal
AND	Logical operator AND
OR	Logical operator OR

Syntax:

-- HAVING
SELECT column_1, aggregate_function(column_2)
FROM table_name
GROUP BY column_1
HAVING condition;

Example: Filter out the customers who spent more than $200.

-- Filter out the customers who spent more than $200. 
SELECT customer_id, SUM(amount)
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 200;

##   customer_id    sum
## 1         526 208.58
## 2         148 211.55

Example: Which store(s) has/have more than 300 customers?

-- Find which stores have more than 300 customers
SELECT store_id, COUNT(customer_id)
FROM customer
GROUP BY store_id
HAVING COUNT(customer_id) > 300;

##   store_id count
## 1        1   326

Example: Filter out the R, G, and PG ratings and then filter out the average rental rates thare are less than $3.0

-- Filter out the R, G, and PG ratings
SELECT rating, AVG(rental_rate)
FROM film
WHERE rating IN ('R','G','PG')
GROUP BY rating
HAVING AVG(rental_rate) <3;

##   rating      avg
## 1      R 2.938718
## 2      G 2.888876

Challenge

Problem: What customers (by customer_id) are eligble for the platinum credit card, which requres the customer to have at least 40 transaction payments?

-- Platnium Card Elgibility
SELECT customer_id, COUNT(amount)
FROM payment
GROUP BY customer_id
HAVING COUNT(amount) >= 40;

##   customer_id count
## 1         144    40
## 2         526    42
## 3         148    45

Problem: When grouped by rating, what movie ratings have an average rental duration of more than 5 days?

-- Which movies have an average rental duration of more than 5 days
SELECT rating, AVG(rental_duration)
FROM film
GROUP BY rating
HAVING AVG(rental_duration) > 5;

##   rating      avg
## 1  NC-17 5.142857
## 2  PG-13 5.053812
## 3     PG 5.082474