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;
