GROUP BY and ORDER BY in SQL

In SQL, data organisation and presentation play an important role in making results more meaningful and easier to interpret. GROUP BY and ORDER BY are two useful clauses that help you to easily interpret the results. Although they are different clause objects, GROUP BY and ORDER BY are often used together to aggregate functions for summarising and ordering data. These SQL clauses can ultimately allow SQL developers and data analysts to generate summarised and formatted reports from large amounts of data. For this reason, it is important to practice and understand how to utilise GROUP BY and ORDER BY. In this blog, we will explore the GROUP BY and ORDER BY clauses in SQL in detail with examples.

Table of Contents:

• Understanding the GROUP BY Clause
  • Why do we need to use the GROUP BY Clause in SQL?
  • GROUP BY with Multiple Columns in SQL
  • Grouping with Aggregate Function in SQL
• Understanding the ORDER BY Clause in SQL
  • Why Do We Need to Use the ORDER BY Clause in SQL?
  • Sorting in Ascending and Descending Order in SQL
  • ORDER BY with Expressions in SQL
• Difference between GROUP BY and ORDER BY Clause
• Common Mistakes When Using GROUP BY and ORDER BY
• Best Practices for Using GROUP BY and ORDER BY in SQL
• Real-World Use Cases of GROUP BY and ORDER BY
• Conclusion

Understanding the GROUP BY Clause

The GROUP BY clause is an SQL clause used to group data. It is commonly used with aggregate functions such as COUNT(), SUM(), AVG(), MAX(), and MIN() to operate on each group of rows separately, rather than on the entire table.

Why do we need to use the GROUP BY Clause in SQL?

• To summarise data based on one or more columns of data.
• To report on patterns like total sales by a region, average score by a module, or number of users by city.
• To group or categorise data that can be used for creating reports and the decision-making process.

Before getting started with the GROUP BY and ORDER BY clauses in SQL, let us create some tables for better understanding.

Let’s create a Learner’s table.

CREATE TABLE Learners (
    lea_id INT PRIMARY KEY,
    f_name VARCHAR(100),
    mail VARCHAR(100)
);

INSERT INTO Learners (lea_id, f_name, mail) VALUES
(1, 'Amit', '[email protected]'),
(2, 'Neha', '[email protected]'),
(3, 'Rajeev', '[email protected]'),
(4, 'Sonal', '[email protected]');

SELECT * FROM Learners;

This is how the Learners table looks once created and inserted with values.

Now, let’s create a Modules Table and insert some values into it.

CREATE TABLE Modules (
    m_id INT PRIMARY KEY,
    m_ttl VARCHAR(100),
    trainer VARCHAR(100),
    duration INT 
);

INSERT INTO Modules (m_id, m_ttl, trainer, duration) VALUES
(201, 'Intro to Databases', 'Kiran Rao', 12),
(202, 'SQL Deep Dive', 'Rajeev Nair', 16),
(203, 'Machine Learning Basics', 'Meena Kapoor', 20),
(204, 'Cloud Fundamentals', 'Anil Mehta', 10);

SELECT * FROM Modules;

This is how the Modules table looks once it is created and inserted with the values.

Finally, let’s create a Registration table by using the Learners and Modules tables as a reference.

CREATE TABLE Reg (
    reg_id INT PRIMARY KEY,
    lea_id INT,
    mod_id INT,
    mode VARCHAR(50),
    score INT,
    hrs_spt INT,
    FOREIGN KEY (lea_id) REFERENCES Learners(lea_id),
    FOREIGN KEY (mod_id) REFERENCES Modules(m_id)
);

INSERT INTO Reg (reg_id, lea_id, mod_id, mode, score, hrs_spt) VALUES
(1, 1, 201, 'Online', 85, 10),
(2, 1, 202, 'Offline', 90, 15),
(3, 2, 203, 'Online', 78, 18),
(4, 3, 201, 'Offline', 88, 12),
(5, 3, 204, 'Online', 95, 9),
(6, 4, 202, 'Online', 92, 14);

SELECT * FROM Reg;

This is how the Registration table looks once it is created and inserted with the values.

Learn SQL from Scratch: Build a Rock-Solid Data Foundation

Unlock the power of databases with hands-on SQL training and become a data-driven professional.

Explore Program

Syntax and Example for GROUP BY Clause in SQL

Syntax:

SELECT col1, AGG_FUN(col2)
FROM tab_name
GROUP BY col1;

Example:

-- To get the highest score of a learner for each module in MySQL

SELECT mod_id, MAX(score) AS MaxScore
FROM Reg
GROUP BY mod_id;

Output:

Explanation: Here, the MAX() function returns the maximum score of a learner for each module. 

GROUP BY with Multiple Columns in SQL

The GROUP BY clause can group multiple columns, where the aggregation can be done using unique combinations of values.

Syntax:

SELECT colA, colB, AGG_FUNC(colC)
FROM tab_name
GROUP BY colA, colB;

Example:

-- To calculate the average of the score and to group by mode and mod_id in MySQL

SELECT mod_id, mode, AVG(score) AS AvgScore
FROM Reg
GROUP BY mod_id, mode;

Output:

Explanation: Here, the query groups data by mod_id and mode together. Then, the AVG(score) is computed for each (mod_id, mode) group.

Grouping with Aggregate Function in SQL

We can group rows by a particular column and retrieve the maximum, minimum, sum, or average value from another column by combining GROUP BY with an aggregate function.

Method 1: Using MAX() with GROUP BY

This MAX() with GROUP BY retrieves the most recent or highest value from each group.

Syntax:

SELECT gp_col, MAX(col) 
FROM table_name
GROUP BY gp_col;

Example:

-- To get the highest score of a learner for each module in MySQL

SELECT mod_id, MAX(score) AS MaxScore
FROM Reg
GROUP BY mod_id;

Output:

Explanation: Here, the MAX() function returns the maximum score of a learner for each module. 

Method 2: Using MIN() with GROUP BY

This MIN() with GROUP BY retrieves the smallest or earliest value from each group.

Syntax:

SELECT gp_col, MIN(col) 
FROM table_name
GROUP BY gp_col;

Example:

-- To get the lowest score of a learner for each module in MySQL

SELECT mod_id, MIN(score) AS MinScore 
FROM Reg
GROUP BY mod_id;

Output:

Explanation: Here, the MIN(score) returns the minimum score of each module that is obtained from the learner.

Method 3: Using SUM() with GROUP BY

This SUM() with GROUP BY calculates the total of a numeric column in each group.

Syntax:

SELECT gro_col, SUM(col) 
FROM tab
GROUP BY gro_col;

Example:

-- To get the total hours spent by a learner for each module in MySQL

SELECT mod_id, SUM(hrs_spt) AS TotalHours
FROM Reg
GROUP BY mod_id;

Output:

Explanation: Here, the SUM(hrs_spt) function sums up the hours spent by all the learners, and then it is grouped by mod_id. 

Method 4: Using AVG() with GROUP BY

This AVG() with the GROUP BY method can be used to calculate the average of numeric values for each group.

Syntax:

SELECT gro_col, AVG(col) 
FROM tab
GROUP BY gro_col;

Example:

-- To get the average score of a learner for each module in MySQL

SELECT mod_id, AVG(score) AS AverageScore
FROM Reg
GROUP BY mod_id;

Output:

Explanation: Here, the AVG(score) function calculates the average performance of the learner for each module.

Method 5: Using COUNT() with GROUP BY

This COUNT() with GROUP BY can be used to count the total number of records in each group.

Syntax:

SELECT gro_col, COUNT(*) 
FROM tab_name
GROUP BY gro_col;

Example:

-- To get the count of a learner for each module in MySQL

SELECT mod_id, COUNT(*) AS TotalRegistrations
FROM Reg
GROUP BY mod_id;

Output:

Explanation: Here, the registrations are grouped by mod_id, and the COUNT(*) function counts the number of learners registered for each module.

Understanding the ORDER BY Clause in SQL

The ORDER BY clause in SQL is used to sort the results of a query according to one or more columns. By default, this sorts in ascending order. You can also sort your query results in descending order.

Why Do We Need to Use the ORDER BY Clause in SQL?

• To implement limit-based queries that demonstrate the top N records  (top 5 scoring learners, etc.)
• To allow time series analysis, order by date and/or timestamp data – e.g, to show the latest registrations or oldest transactions.
• To sort or rank rows using numeric or text data. 

Syntax and Example for ORDER BY Clause in SQL

Syntax:

SELECT col1, col2
FROM tab
ORDER BY col1 [ASC | DESC], column2 [ASC | DESC];

Parameters:

• ASC: Ascending order 
• DESC: Descending order

Example:

-- To list the names of the learners in alphabetical order in MySQL

SELECT * FROM Learners
ORDER BY f_name;

Output:

Explanation: Here, the ORDER BY f_name sorts the names from the learners table in an ascending order, which sorts the names in alphabetical order.

Sorting in Ascending and Descending Order in SQL

Using the ORDER BY clause, the specified column can be ordered in either ascending or descending order.

Sorting in Ascending Order

Ascending order (ASC) sorts the result from smallest to largest while alphabetically arranging text from A to Z and arranging numbers from lowest to highest.

Syntax:

SELECT col1, col2
FROM tab
ORDER BY col1 ASC;

Example:

-- To sort in ascending order in MySQL

SELECT lea_id, mod_id, score
FROM Reg
ORDER BY score ASC;

Output:

Explanation: Here, this query sorts registrations by score from lowest to highest. 

Sorting in Descending Order

Descending order (DESC) sorts the result from largest to smallest while alphabetically arranging text from Z to A and numerically from highest to lowest.

Syntax:

SELECT col1, col2
FROM tab
ORDER BY col1 DESC;

Example:

-- To sort in descending order in MySQL

SELECT lea_id, mod_id, score
FROM Reg
ORDER BY score DESC;

Output:

Explanation: Here, this query sorts registrations by score from highest to lowest.

ORDER BY with Expressions in SQL

You may also sort your results using ORDER BY any expressions or column aliases that have been defined in the SELECT clause.

Syntax:

SELECT col1, expr AS alias
FROM tab
ORDER BY alias [ASC | DESC];

Example:

-- To order by with expressions in MySQL

SELECT lea_id, SUM(hrs_spt) AS TotalTime
FROM Reg
GROUP BY lea_id
ORDER BY TotalTime DESC;

Output:

Explanation: Here, SUM(hrs_spt) is calculated, and the alias name is given as TotalTime. The ORDER BY TotalTime DESC statement sorts the results using this calculated value, so learners who spent the most time are returned first.

Difference between GROUP BY and ORDER BY Clause

Common Mistakes When Using GROUP BY and ORDER BY

• Choosing Columns That Are Not GROUP BY or Aggregated

SELECT emp_name, region, SUM(amount)
FROM Sales
GROUP BY emp_name;

The region is not aggregated and is not in the GROUP BY, which is an error in standard SQL.

• Ordering Before Grouping

SELECT emp_name, SUM(amount)
FROM Sales
GROUP BY emp_name
ORDER BY SUM(amount);

You must have GROUP BY before ORDER BY.

• Using Aliases in GROUP BY

SELECT emp_name, SUM(amount) AS TotalSales
FROM Sales
GROUP BY TotalSales;

It’s advisable to avoid using aliases in GROUP BY.

Best Practices for Using GROUP BY and ORDER BY in SQL

• Correct Use of Aggregate Functions with GROUP BY: Make sure that any column names in the SELECT list that are not aggregates are also listed with GROUP BY.
• Use Minimal Columns for GROUP BY: Only use the columns that you need to use for grouping, so that GROUP BY has a small number of columns to deal with.
• Use Aliases in the ORDER BY: You can use aliases to order results instead of repeating the expressions.
• Use ORDER BY for ranking the rows: Ordering will keep the data grouped in such a way that you can interpret it with relevance (for example, top by, total by, etc.).
• Index Columns Used in GROUP BY or ORDER BY: Indexes on grouped/sorted columns will increase performance when used often, especially in large tables.

Real-World Use Cases of GROUP BY and ORDER BY

1. Sales Performance Report

A company wants to analyse the performance of its sales by grouping the data and ordering it by total revenue.

Example:

CREATE TABLE Sales (
    s_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    reg VARCHAR(50),
    amnt DECIMAL(10,2),
    s_dt DATE
);

INSERT INTO Sales (s_id, emp_name, reg, amnt, s_dt) VALUES
(1, 'Karan Mehta', 'North', 15000.00, '2024-12-01'),
(2, 'Sneha Rao', 'South', 18000.50, '2024-12-03'),
(3, 'Karan Mehta', 'North', 12000.00, '2024-12-05'),
(4, 'Ayesha Iqbal', 'East', 22000.75, '2024-12-06'),
(5, 'Sneha Rao', 'South', 19500.00, '2024-12-10'),
(6, 'Ayesha Iqbal', 'East', 20000.00, '2024-12-12'),
(7, 'Rohan Nair', 'West', 9000.00, '2024-12-14');

-- To group the sales records by each salesperson and order by total revenue from high to low

SELECT emp_name, SUM(amnt) AS TotalSales
FROM Sales
GROUP BY emp_name
ORDER BY TotalSales DESC;

Output:

Explanation: Here, the GROUP BY emp_name clause is used to group all sales transactions by related employee. The SUM(amount) function gives us the total sales done by each associated employee, and the ORDER BY TotalSales DESC clause will sort the final output by TotalRevenue from highest to lowest.

2. Course Feedback Analysis

Utilise GROUP BY to classify feedback ratings to students by course, then ORDER BY to rank courses based on average learner rating. 

Example:

CREATE TABLE Fb (
    fb_id INT PRIMARY KEY,
    c_ttl VARCHAR(100),
    l_name VARCHAR(100),
    rate INT,  
    fb_dt DATE
);

INSERT INTO Fb (fb_id, c_ttl, l_name, rate, fb_dt) VALUES
(1, 'Python Basics', 'Divya Nair', 4, '2025-03-01'),
(2, 'SQL for Analysts', 'Arjun Verma', 5, '2025-03-02'),
(3, 'Python Basics', 'Maya Sen', 3, '2025-03-04'),
(4, 'Machine Learning Intro', 'Vikas Jain', 5, '2025-03-06'),
(5, 'SQL for Analysts', 'Rekha Das', 4, '2025-03-07'),
(6, 'Machine Learning Intro', 'Isha Pillai', 5, '2025-03-08'),
(7, 'Python Basics', 'Ritesh Babu', 4, '2025-03-09');

-- To find the average rating of each course and to rank the courses by rating from high to low

SELECT c_ttl, AVG(rate) AS AvgRating
FROM Fb
GROUP BY c_ttl
ORDER BY AvgRating DESC;

Output:

Explanation: Here, the GROUP BY c_ttl groups feedback for each course, AVG(rate) calculates the average learner rating for each course, and then it orders by high to low using ORDER BY AvgRating DESC.

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career—without spending a dime!

Explore Program

Conclusion 

The GROUP BY and ORDER BY clauses are critical clauses in SQL for tabulating and sorting results, respectively. GROUP BY allows us to group and summarise data into useful groups, while ORDER BY is used to present the summarised group results in a logical and readable order. Having an understanding of the syntax, best practices, and avoiding mistakes, you will be able to write correct SQL queries. In this blog, you have gained knowledge on GROUP BY and ORDER BY clauses in detail.

To learn more about SQL functions, check out this SQL course and also explore SQL Interview Questions prepared by industry experts

GROUP BY And ORDER BY in SQL – FAQs