1. Introduction
In this article, we will look at the SQL Group by Clause. We can use this clause with all the databases that support SQL. We will also see how we use Group BY in the MySQL database.
You can also check this tutorial in the following video:
2. SQL Group By Clause
The SQL Group By is a clause that clumps data into groups based on a shared value. We can use it with the Where clause and the Having clause and aggregate functions. The Basic syntax for the Group By Clause:
SELECT
FROM
WHERE
where_conditions
GROUP BY c1 , c2,...,cn;
3. Setup for examples of Group by
To understand Group by clause, we will run examples using the database “Sakila.” The Sakila database is an example database given by MySQL. The schema structure is available on the documentation page. For running the queries, we will use the MySQL Workbench.
4. Examples with the SQL Group by Clause
We will see various examples with the Group by clause
4.1 Simple Group by
We can use the Group by clause without any conditions. To use the Group By clause, we need to include the column name in the Select statement.
select rental_rate,length from film group by length;
4.2 Group by with Count
We can use the group by with aggregate functions like count(),max(), min(). For example, with the Count function
select count(film_id),category_id from film_Category group by category_id;
4.3 Group by clause with Order by
We can club the Group by clause with the Order by clause. The Order by clause always has to be the last statement in a query and so has to come after the Group By.
select avg(amount),staff_id from payment group by staff_id order by staff_id desc;
4.4 Group by Clause with Alias
We can group records using a column alias. The only rule is that the alias needs to be for a column and not any aggregate functions
Select avg(replacement_cost), rating R from film group by R;
4.5 Group by with Having Clause in MySQL
We filter records with the aggregate function using the Having Clause. We can club these filtered records further using the Group By Clause. Using the Group by Clause with the Having Clause has a rule that we need to use the columns present in the Select or Having Clause. If we use a different column, it results in an error. For example:
select avg(replacement_cost),rating from film group by rating having rental_duration >= 3;
The correct query is:
select avg(replacement_cost),rating from film group by rental_duration having rental_duration >= 3;
4.6 Group By with Multiple Columns
We can also Group records based on more than one column. In such a case, SQL will group the records based on all the columns listed. So, if we have a clause:
GROUP BY column1,column2
Then SQL will group records that fit the criteria: column1 AND column2. For example, if we Group by just by one column, then the result is as follows:
select Count (*), language_id from film group by language_id;
However, if we use two columns in the Group by then, the result is:
select count(*), language_id,rental_duration from film group by language_id,rental_duration;
5. Summary
In the article, we saw syntaxes related to the Group By by Clause in MySQL. We used the SQL Group By Clause to group records together based on the conditions applied.
6. Download the Source Code
This example was of the Group by Clause.
You can download the full source code of this example here:
SQL GROUP BY Statement
Last updated on Jan. 21st, 2022
