MySQL GROUP BY Clause

The GROUP BY clause groups the returned record set by one or more columns. You specify which columns the result set is grouped by.

Consider the following result set:

We can see that the last_name column contains a lot of duplicates — many actors share the same last name.

Now, if we add GROUP BY last_name to the mix:

SELECT last_name 

FROM sakila.actor

GROUP BY last_name;

This returns the following result:

We have selected all actors' last names from the table and grouped them by the last name. If two or more actors share the same last name, it is represented only once in the result set. For example, if two actors have a last name of "Bailey", that last name is listed once only.

Using COUNT() with GROUP BY

A benefit of using the GROUP BY clause is that you can combine it with aggregate functions and other clauses to provide a more meaningful result set.

For example, we could add the COUNT() function to our query to return the number of records that contain each last name.

Example:

SELECT last_name, COUNT(*) 

FROM sakila.actor

GROUP BY last_name;

This returns the following result:

We can see that there are three actors with a last name of "ALLEN", one with "ASTAIRE", one with "BACALL" etc.

Here we are using the GROUP BY clause to group by the last_name field. This means that, if two or more records share the same last name, they are grouped into one. The second column tells us how many records were found with that last name (we use the COUNT() function to determine this). So if there are two "Bailey"'s in the table, we'll see the number "2" in the second column.

Using SUM() with GROUP BY

This example uses another aggregate function — this time the SUM() function.

This is a slightly more complex query that uses GROUP BY within a query that returns data from two tables.

SELECT  c.customer_id AS 'Customer ID', 

c.first_name AS 'First Name', 

c.last_name AS 'Last Name', 

       SUM(amount) AS 'Total Paid'

FROM sakila.payment p

INNER JOIN customer c ON

p.customer_id = c.customer_id

GROUP BY c.customer_id;

Result:

In the above example, we return data from two tables (payment and customer), then group the results by customer ID (customer_id).

We use an INNER JOIN to return data from both tables. We also provide a column heading using the AS clause. And we use the SUM() function to provide each total.

MySQL vs Standard SQL

MySQL generally aims to follow the SQL standards (ANSI SQL and ODBC SQL) as closely as possible. However, there are some differences between MySQL and the SQL standard/s.

The GROUP BY clause is one case where MySQL has differed slightly from standard SQL. Not only that, different versions of SQL handle GROUP BY different.

If you're interested, here's what the MySQL documentation says about MySQL handling of GROUP BY.