5) SQL Commands for Querying Databases Lesson

GROUP BY

3 min to complete · By Ryan Desmond

The GROUP BY SQL query allows you to group rows of query results by one or more columns. This lesson provides examples and syntax for the SQL GROUP BY function.

Colorful illustration of a light bulb

Note: This lesson uses the MySQL Sakila demo database. If you haven't already, install the Sakila DB, then head back here to run the following examples.

How to Use Group By in SQL

SQL GROUP BY is used with aggregate functions such as COUNT(), SUM(), and AVG() to group the result set by one or more columns. After writing the GROUP BY statement, you follow it by the column that you want to be grouped.

SQL Group By Examples

Take a look at two easy SQL GROUP BY examples.

Group By Actor

Suppose you want to know the total number of films each actor has been in. In order to do this, you need to GROUP BY a field such as actor_id. For instance:

SELECT a.first_name, a.last_name, COUNT(a.actor_id) as num_films
FROM actor a
JOIN film_actor fa
    ON a.actor_id = fa.actor_id
JOIN film f
    ON fa.film_id = f.film_id
GROUP BY a.actor_id
ORDER BY num_films DESC;
Join actor and film table and group by actor_id

Group By Customer

Or, suppose you want to figure out who your best customer is:

SELECT c.first_name, c.last_name, SUM(p.amount) as total_payments
FROM customer c
JOIN payment p
ON c.customer_id = p.customer_id
GROUP BY c.customer_id
ORDER BY total_payments DESC;
Join customer and payment table and group by customer_id

Summary: SQL Group By

  • The SQL GROUP BY query allows you to group rows of query results by one or more columns
  • The GROUP BY SQL statement comes after the final table has been selected and joined
  • GROUP BY will be written before some statements, such as ORDER BY

Group By SQL Syntax

Here's the syntax for the SQL GROUP BY query, where you can substitute the variables in < > with your values.

SELECT <YOUR_COLUMNS>
FROM <YOUR_TABLE>
GROUP BY <YOUR_COLUMN>

That was quick. Well done!

Next up is a video demo of how to use SQL GROUP BY.