Home » Core Java » sql » SQL GROUP BY Statement

About Reshma Sathe

I am a recent Master of Computer Science degree graduate from the University Of Illinois at Urbana-Champaign.I have previously worked as a Software Engineer with projects ranging from production support to programming and software engineering.I am currently working on self-driven projects in Java, Python and Angular and also exploring other frontend and backend technologies.

SQL GROUP BY Statement

1. Introduction

In this article, we will look at the Group by Clause. We can use the Group By clause with all the databases that support SQL. We will see how we use Group BY in the MySQL database.

2. SQL Group By Clause

The Group By is a clause that clumps data into groups based on a shared value. We can use the Group By Clause 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;
Simple SQL Group by example
Simple Group by

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;
sql Group by aggregate functions
Group by aggregate functions

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;
Order by with sql group by
Order by with group by

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;
Group by with alias
Group by with alias

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;
Incorrect group by query with having clause
Incorrect group by query with having clause

The correct query is:

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

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;
Group by with a single column
Group by with a single column

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;
Group by with a multiple columns
Group by with multiple columns

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.

Download
You can download the full source code of this example here: SQL GROUP BY Statement

Do you want to know how to develop your skillset to become a Java Rockstar?

Subscribe to our newsletter to start Rocking right now!

To get you started we give you our best selling eBooks for FREE!

 

1. JPA Mini Book

2. JVM Troubleshooting Guide

3. JUnit Tutorial for Unit Testing

4. Java Annotations Tutorial

5. Java Interview Questions

6. Spring Interview Questions

7. Android UI Design

 

and many more ....

 

Receive Java & Developer job alerts in your Area

 

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 Comments
Inline Feedbacks
View all comments