Header

Grouping with `GROUP BY` - Assignments

1. Grouping by first name of actors

Complete the following query to group by the first name of actors (column first_name) from the actors table.

Count the number of rows.

This will show you how many actors have a certain first name.

Use the GROUP BY statement to group.
Use the COUNT() function to count the number of rows.

SELECT
  first_name,
  COUNT(*)
FROM
  actors
GROUP BY
  first_name;

first_name	count
Giovanni	1
Carly	1
Ewan (I)	1
...	...
Edward	3
Liam	5
...	...

Columns: 2
Rows: 1,198

2. Renaming a column

In the previous assignment, we determined how often a certain first name appears in the actors table.

From GROUP BY and COUNT(), the new column is automatically named count.

We will change this by renaming the column.

Reuse your code from the previous assignment.
Rename the column count to first_name_count.
Use the AS statement.

SELECT
  first_name,
  COUNT(*) AS first_name_count
FROM
  actors
GROUP BY
  first_name;

first_name	first_name_count
Giovanni	1
Carly	1
Ewan (I)	1
...	...
Edward	3
Liam	5
...	...

Columns: 2
Rows: 1,198

3. Sorting after grouping

In the previous assignments, we determined how often a certain first name appears in the actors table, and we changed the column name.

You can't easily see which first name appears most often yet. This is because it hasn't been sorted yet.

We will improve this by sorting on the column first_name_count.

Reuse your code from the previous assignment.
Sort descending on the column first_name_count.
- Use the ORDER BY statement.

Which name appears most often?

SELECT
  first_name,
  COUNT(*) AS first_name_count
FROM
  actors
GROUP BY
  first_name
ORDER BY
  COUNT(*) DESC;

first_name	first_name_count
John	32
Michael	23
James	15
...	...

Columns: 2
Rows: 1,198

4. Filtering after grouping

So far, we have done: * Grouping on column first_name. * Changing column name count to first_name_count. * Sorting descending on the new column.

We will now apply a filter after sorting. So that only names that appear more than 10 times are shown.

Reuse your code from the previous assignment.
Filter on the new column.
- Use the HAVING statement.
  - Make sure to apply this in the correct place.

SELECT
  first_name,
  COUNT(*) AS first_name_count
FROM
  actors
GROUP BY
  first_name
HAVING
  COUNT(*) > 10
ORDER BY
  COUNT(*) DESC;

first_name	first_name_count
John	32
Michael	23
James	15
Peter	15
David	14
John (I)	14
Mark	13
Richard	11
Steve	11

Columns: 2
Rows: 9

5. (Extra) Grouping by first name and gender

So far, we have grouped by first name for both female and male actors. This without making a distinction.

Now write a query to group by column first_name and also by column gender.

Select existing columns first_name and gender.
Group by columns first_name and gender.
Count the number of rows with COUNT(*)
Sort:
- On the new column, descending.
- On gender, ascending.

SELECT
  first_name,
  gender,
  COUNT(*) AS first_name_count
FROM
  actors
GROUP BY
  gender, first_name
ORDER BY
  COUNT(*) DESC,
  gender ASC;

first_name	gender	first_name_count
John	M	32
Michael	M	23
James	M	15
...	...	...

Columns: 3
Rows: 1,203

6. (Extra) Most common female first names

So far, we have grouped by first name for both female and male actors.

Now write a query to obtain the top 10 most common female first names.

SELECT
  first_name,
  COUNT(*) AS first_name_count
FROM
  actors
WHERE
  gender = 'F'
GROUP BY
  first_name
ORDER BY
  COUNT(*) DESC
LIMIT
  10;

first_name	first_name_count
Maria	6
Jennifer	5
Karen	4
Linda	4
Amy	4
Julie	4
Lori	4
Julia	4
Alexandra	3
Lisa	3

Columns: 2
Rows: 10

Grouping with GROUP BY - Assignments