MySQL Group By Clause

MySQL Group by clumps together sets or rows and gives back a summary of these rows. A Group by clause is used with Aggregate functions and the having clause.

MySQL Group By clause syntax

The basic syntax of a group by is:

SELECT
    <aggregate_function(col_name)>
FROM
    <table-name>
WHERE
    where_conditions
GROUP BY c1 , c2,...,cn;

Next, we will look at some examples using the group by clause.

Simple 'group by' in MySQL

We can use the group by clause without any conditions.

select rental_rate,length 
from film
group by length;

Group by with count in MySQL

We can use the group by with aggregate functions like count(),max(), min().   

select count(film_id),category_id
from film_Category
group by category_id;

Group by clause with Order by in MySQL

We can also use the group by clause with the order by clause. The order by needs to be put after the group by clause. The order by clause has to be the last statement in a query always.

select avg(amount),staff_id from payment
group by staff_id
order by staff_id desc;

Group by Clause with Alias in MySQL

MySQL supports grouping records together using an alias for a column. However, the alias needs to be for a column and not an aggregate function.

​select avg(amount),staff_id from payment
group by staff_id
order by staff_id desc;

​

Group by with Having Clause in MySQL

 To filter records using aggregate functions we need to use the having clause. While using the having clause we need to use the column names present in the select or the group by clause. Writing a having clause on any other column results in an error. 

For example,

select avg(replacement_cost),rating 
from film
group by rating
having rental_duration >= 3;

To correct the above query, we need to have,

select avg(replacement_cost),rating 
from film
group by rental_duration
having rental_duration >= 3;