Aggregation

• Suppose that we want to count the number of different languages.

SELECT COUNT(*) FROM language
Copy

• In this example, all tuples in the table “language” is considered as a group. The query counts the number of distinct objects in the group.
  After applying an aggregation function, the result of COUNT(*) is treated as an attribute.

• For example, find the number of films for each language. The resulting table has two columns, one for languages, one for the number of films.

Only those films of the same language are in the same group (different language different group).
Then, count the number within each group.

• To achieve the grouping, we use GROUP BY.

SELECT language_id, count(film_id) 
FROM film JOIN language USING(language_id) 
GROUP BY (language_id) 
Copy

• To indicate the result of aggregation of each group, group by attributes are usually selected.

• Sometimes we need condition checking before and after aggregation functions

• For example, show the actors’ names and the number of Sci-Fi movies played by him/her if the number of Sci-Fi movies is more than 3

SELECT actor_id,first_name,last_name,COUNT(film_id)
FROM actor JOIN film_actor USING (actor_id) 
    JOIN film USING(film_id) 
    JOIN film_category USING(film_id)
    JOIN category USING(category_id)
WHERE category.name='sci-fi'
GROUP BY actor_id
HAVING COUNT(film_id)>3;
Copy

• The query is executed in this sequence.

1. Combine the tables in FROM clause
2. Check the condition in WHERE clause
3. Group tuples
4. Aggregation function
5. Check the condition in HAVING clause
6. Output the selected attributes

Assuming that every country has a city. Why the following query cannot find the number of countries?

SELECT COUNT(country_id)
FROM city
Copy

We only assume that every country has a city. But, a country may have multiple cites.
Those countries will be over counted.
We can use DISTINCT to remove duplications.

SELECT COUNT(DISTINCT country_id)
FROM city
Copy