WHERE절의 조건은 <FROM 테이블명>으로 지정된 테이블을 대상으로 한다.
GROUP BY 절에 의해서 생긴 각 그룹에 대해서 조건을 적용하려면 HAVING절을 사용해야 한다.
SQL_QUERY = """
SELECT category.name, COUNT(*) AS category_film_count
FROM film_category
INNER JOIN category
ON category.category_id = film_category.category_id
WHERE (category.name = 'Comedy' OR category.name = 'Sports' OR category.name = 'Family')
GROUP BY category.category_id
"""
pd.read_sql(SQL_QUERY, db)
name | category_film_count | |
---|---|---|
0 | Comedy | 58 |
1 | Family | 69 |
2 | Sports | 74 |
SQL_QUERY = """
SELECT category.name, COUNT(*) AS category_film_count
FROM film_category
INNER JOIN category
ON category.category_id = film_category.category_id
GROUP BY category.category_id
"""
pd.read_sql(SQL_QUERY, db)
name | category_film_count | |
---|---|---|
0 | Action | 64 |
1 | Animation | 66 |
2 | Children | 60 |
3 | Classics | 57 |
4 | Comedy | 58 |
5 | Documentary | 68 |
6 | Drama | 62 |
7 | Family | 69 |
8 | Foreign | 73 |
9 | Games | 61 |
10 | Horror | 56 |
11 | Music | 51 |
12 | New | 63 |
13 | Sci-Fi | 61 |
14 | Sports | 74 |
15 | Travel | 57 |
SQL_QUERY = """
SELECT category.name, COUNT(*) AS category_film_count
FROM film_category
INNER JOIN category
ON category.category_id = film_category.category_id
GROUP BY category.category_id
HAVING COUNT(*) > 70
"""
pd.read_sql(SQL_QUERY, db)
name | category_film_count | |
---|---|---|
0 | Foreign | 73 |
1 | Sports | 74 |