What Is The Use Of SQL GROUP BY Statement?

When a large amount of data is present, we often see a possibility to manipulate data according to our requirements. GROUP BY clause is one such statement in SQL, used to group the data based on few columns, or on conditions. In this article on SQL GROUP BY Statement, I will discuss a few ways to use the GROUP BY statements in the following sequence:

1. GROUP BY statement 
2. Syntax
3. Examples:
   • Use GROUP BY on single column
   • GROUP BY on multiple columns
   • Use GROUP BY with ORDER BY
   • GROUP BY with HAVING clause
   • Use GROUP BY with JOINS

Before we move onto the examples on how to use the GROUP BY clause, let us understand what is GROUP BY in SQL and its syntax.

SQL GROUP BY statement 

This statement is used to group records having the same values. The GROUP BY statement is often used with the aggregate functions to group the results by one or more columns. Apart from this, the GROUP BY clause is also used with the HAVING clause and JOINS to group the result set based on conditions.

SQL GROUP BY Syntax

SELECT Column1, Column2,..., ColumnN
FROM TableName
WHERE Condition
GROUP BY ColumnName(s)
ORDER BY ColumnName(s);

Here, you can add the aggregate functions before the column names, and also a HAVING clause at the end of the statement to mention a condition. Next, in this article on SQL GROUP BY, let us understand how to implement this statement.

Examples:

For your better understanding, I have divided the examples into the following sections:

1. • Use GROUP BY on single column
   • GROUP BY on multiple columns
   • Use GROUP BY with ORDER BY
   • GROUP BY with HAVING clause
   • Use GROUP BY with JOINS

I am going to consider the following table to explain to you the examples:

Let us take a look at each one of them.

Use SQL GROUP BY on single column

Example: 

Write a query to retrieve the number of employees in each city.

SELECT COUNT(EmpID), City
FROM Employees
GROUP BY City;

Output:

You will see the following output:

Use SQL GROUP BY on multiple columns

Example: 

Write a query to retrieve the number of employees having different salaries in each city.

SELECT City, Salary, Count(*)
FROM Employees
GROUP BY City, Salary;

Output:

The table will have the following data:

Use SQL GROUP BY with ORDER BY

When we use the SQL GROUP BY statement with the ORDER BY clause, the values get sorted either in ascending or descending order.

Example: 

Write a query to retrieve the number of employees in each city, sorted in descending order.

SELECT COUNT(EmpID), City
FROM Employees
GROUP BY City
ORDER BY COUNT(EmpID) DESC;

Output:

The table will have the following data:

Find out our MS SQL Course in Top Cities

Use SQL GROUP BY with HAVING clause

The SQL GROUP BY statement is used with ‘HAVING’ clause to mention conditions on groups. Also, since we cannot use the aggregate functions with the WHERE clause, we have to use the ‘HAVING’ clause to use the aggregate functions with GROUP BY.

 Example: 

Write a query to retrieve the number of employees in each city, having salary > 15000

SELECT COUNT(EmpID), City
FROM Employees
GROUP BY City
HAVING SALARY > 15000;

Output:

Since all are records in the Employee table have a salary > 15000, we will see the following table as output:

Use GROUP BY with JOINS

JOINS are SQL statements used to combine rows from two or more tables, based on a related column between those tables. We can use the  SQL GROUP BY statement to group the result set based on a column/ columns. Consider the below tables to execute the JOIN statements with the SQL GROUP BY clause.

Projects Table:

Clients Table:

Example

Write a query to list the number of projects requested by each client:

SELECT Clients.ClientName, COUNT(Projects.ProjectID) AS RequestedProjects FROM Projects
LEFT JOIN Clients ON Projects.ProjectID = Clients.ProjectID
GROUP BY ClientName;

Output:

The table will have the following data:

With that, we come to an end of the SQL GROUP BY article. Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network of more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions, etc. End of the training you will be able to create and administer your own MySQL Database and manage data.

Learn to query and manage databases like a pro in our SQL Training.

Got a question for us? Please mention it in the comments section of this “SQL GROUP BY” article and we will get back to you as soon as possible.