SQL GROUP BY

In this SQL tutorial, we will learn how to use GROUP BY in SQL. Group By in SQL is used to arrange similar data into groups.  We can GROUP BY in generating reports (eg: sales, attendance), analyzing grouped statistics, and filtering aggregated results using the HAVING clause.

Table of Contents

• GROUP BY Clause in SQL
• Examples of GROUP BY in SQL
• HAVING Clause in GROUP BY Clause
• GROUP BY Clause with JOIN

GROUP BY Clause in SQL

It is used to arrange similar data into groups. The GROUP BY clause follows the WHERE clause and comes before the ORDER BY clause.

• • We can use a SELECT statement with a GROUP BY clause.

• • We have to use the GROUP BY clause before the ORDER BY clause.

The GROUP BY statement is utilized to group rows with similar values into summary rows, such as finding out the total number of Apples in each store. It is commonly combined with aggregate functions like COUNT(), MAX(), MIN(), SUM(), and AVG() to group the result set based on one or more columns.

Syntax of GROUP BY in SQL

SELECT column1, column2
FROM table_name
WHERE [condition]
GROUP BY column1, column2
ORDER BY column1, column2;

Examples of GROUP BY in SQL

Let’s understand the working of the GROUP BY clause with an example:

Suppose we have a table Orders, in which we have given the Customer, Product, quantity, price, and their Region. With the help of this table, let’s understand the GROUP BY clause in detail.

Table: Orders

Example 1: GROUP BY Single Column

Let’s group the data on the basis of region (a single column).

Query:

SELECT Region, SUM(Quantity) AS TotalQuantity

FROM Orders

GROUP BY Region;

Output:

Explanation: Here we have combined the total number of mobile phones sold from different regions (North, South, and East). The SUM() is used to find the sum of the product quantities.

Master SQL for Data Mastery!

Learn Industry-Level SQL Skills to Analyze, Manage, and Unlock the Power of Data

Explore Program

Example 2: GROUP BY  Multiple Columns

Group By multiple columns, means grouping is done on the basis of multiple columns.

Query:

SELECT Region, Customer, SUM(Quantity) AS TotalQuantity

FROM Orders

GROUP BY Region, Customer;

Output:

Explanation: Here we have grouped two columns, region and customer, and found the total number of products bought by them. The SUM() is used to find the sum of the product quantities.

Example 3:  GROUP BY with ORDER BY Clause

Let’s learn GROUP BY with ORDER BY Clause with an example:

Query: 

SELECT  Region, SUM(Quantity) AS TotalQuantity

FROM  Orders

GROUP BY Region ORDER BY TotalQuantity DESC;

Output:

Explanation: Here we have found out the region and total number of products sold and sorted the data in descending order. (that is South people have bought the maximum 5 products, and North people have bought the second maximum 6 products, and East have the least one 5. )

HAVING Clause in GROUP BY Clause

The WHERE clause is used to filter data from a database. It can be used to select specific rows, columns, and values from a table. We can use the HAVING clause to give a condition and filter the data accordingly.

Syntax:

SELECT column1, column2, aggregate_function(column3)
FROM table_name
GROUP BY column1, column2
HAVING condition;

Example: HAVING Clause in GROUP BY Clause

Let’s understand the HAVING clause in GROUP BY clause with an example:

Query:

SELECT Region, 
       SUM(Quantity) AS TotalQuantity
FROM Orders
GROUP BY Region
HAVING SUM(Quantity) > 5;

Output:

Explanation: Here we have sorted the data based on the condition that the sum of the products should be greater than 5, so the HAVING clause filters the data accordingly.

Free SQL Course – Master Data Management for Industry Success!

Gain in-demand skills to manage and analyze data effectively

Explore Program

GROUP BY Clause with JOIN

SQL JOINs refer to a clause in SQL that is used to combine rows of two or more tables by using common values. It takes into consideration the records from two or more tables in a database and combines them. SQL JOIN is typically used in situations where you want to access one or more tables through a select statement.

Let’s learn GROUP BY Clause with JOIN with an example:

Example Tables

Table 1: Orders

Table 2: Customers 

Query:

SELECT
c.CustomerName,
SUM(o.Quantity) AS TotalQuantity
FROM
Orders o
JOIN
Customers c
ON
o.CustomerID = c.CustomerID
GROUP BY
c.CustomerName;

Output:

Explanation: Here we have joined two tables Orders and Customers and group the data of two tables on the basis of customer name and then found out the total Quantity Ordered per Customer.

Conclusion

In Conclusion, the GROUP BY clause is used to group identical rows. By grouping rows with similar values and applying aggregate functions like COUNT(), MAX(), MIN(), SUM(), and AVG() we can extract large datasets.  So far in this article, we have learned how to use the GROUP BY clause, and its example in more detail. If you want to dive deeper into SQL and advance your career, consider enrolling in Intellipaat’s SQL Course.