AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (14 Blogs)
  • MySQL (43 Blogs)
  • SQL Essentials Training and Certification (6 Blogs)
SEE MORE

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-widget-banner-bg

What Is The Use Of SQL GROUP BY Statement?

Published on Oct 07,2019 40 Views
24 / 32 Blog from Introduction to SQL

MI-new-launch

myMock Interview Service for Real Tech Jobs

myMock-mobile-banner-bg

myMock Interview Service for Real Tech Jobs

  • Mock interview in latest tech domains i.e JAVA, AI, DEVOPS,etc
  • Get interviewed by leading tech experts
  • Real time assessment report and video recording

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:

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:

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

EmpIDEmpNameEmpEmailPhoneNumberSalaryCity

1

Nidhi

nidhi@sample.com

9955669999

50000

Mumbai

2

Anay

anay@sample.com

9875679861

55000

Pune

3

Rahul

rahul@sample.com

9876543212

35000

Delhi

4

Sonia

sonia@sample.com

9876543234

35000

Delhi

5

Akash

akash@sample.com

9866865686

25000

Mumbai

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:

Count(EmpID)City

2

Delhi

2

Mumbai

1

Pune

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:

CitySalaryCount(*)

Delhi

35000

2

Mumbai

25000

1

Mumbai

50000

1

Pune

55000

1

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:

Count(EmpID)City

2

Delhi

2

Mumbai

1

Pune

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:

Count(EmpID)City

2

Delhi

2

Mumbai

1

Pune

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:

ProjectIDEmpIDClientIDProjectDate
23451426-01-2019
98762528-02-2019
34563612-03-2019

Clients Table:

ClientIDClientName

4

Sanjana

5

Rohan

6

Arun

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:

ClientNameRequestedProjects

Arun

1

Rohan

1

Sanjana

1

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.

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.

Comments
0 Comments

Browse Categories

Subscribe to our Newsletter, and get personalized recommendations.