Difference Between Order By and Group By Clause in SQL

SQL provides powerful tools for organising and analysing data, and two commonly used clauses are ORDER BY and GROUP BY. The ORDER BY clause is used to sort data, while the GROUP BY clause is used to group rows with similar values, often combined with aggregate functions like SUM(), AVG(), or COUNT().

Understanding the difference between these clauses is crucial for writing efficient and accurate SQL queries. In this article, we will explore the differences between these clauses, their syntax, and practical examples to help us use them effectively.

Difference Between ORDER BY and GROUP BY Clause

The following table summarises the key differences:

Order By

Order by keyword sort the result-set either in ascending or descending order. This clause sorts the result set in ascending order by default. In order to sort the result-set in descending order DESC keyword is used.

Syntax:

SELECT column_1, column_2, column_3...........
FROM Table_Name
ORDER BY column_1, column_2, column_3....... ASC|DESC;

Key Terms

• Table_Name: Name of the table.
• ASC: keyword for ascending order.
• DESC: keyword for descending order.

Employees table creation

Here, we create an employees table with employee_id,first_name, and salary and with the help of order by clause, we can sort the element by their specific column.

Query:

create table employees (
employee_id INT PRIMARY KEY ,
first_name VARCHAR(50) ,
salary INT
);

INSERT INTO employees(employee_id,first_name,salary)
VALUES
(100,'Steven',24000),
(101,'Neena',17000),
(102,'Lex',17000),
(103,'John',11000),
(104,'Robert',12000),
(105,'Leo',10000);

Output:

Sorting in Ascending Order

It sorts the records automatically in ascending order if we want to show the records in descending order then we use DESC.

Query:

select * from employees ORDER BY salary;

Output:

Sorting in Descending Order

The ORDER BY salary DESC query sorts the rows in the employees table based on the salary column in descending order, displaying the highest salaries first.

Query:

select * from employees ORDER BY salary DESC; 

Output

Group By

Group by statement is used to group the rows that have the same value. It is used with aggregate functions for example AVG(), COUNT(), SUM()etc. One thing is to remember about the group by clause that the tuples are grouped based on the similarity between the attribute values of tuples.

Syntax

SELECT function_Name(column_1), column_2
FROM Table_Name
WHERE condition
GROUP BY column_1, col umn_2
ORDER BY column_1, column_2;

Key Terms

• function_Name: Name of the aggregate function (SUM(), AVG(), COUNT(), etc.)
  
• Table_Name: Name of the table.
  
• GROUP BY: Groups rows with the same values in the specified columns.

Using COUNT()

The COUNT() function is used when we need to return the total number of rows that are stored in the database. So the example for the COUNT() function is

Query:

SELECT COUNT(Salary) from employee;

Output

Using AVG()

The AVG() function calculates the average value of a specified column. This query computes the average salary of all employees in the employees table.

Query:

SELECT AVG(Salary) from employees;

Output

Using SUM()

The SUM() function calculates the total sum of a specified column. this query returns the total salary paid to all employees in the employees table.

Query:

SELECT SUM(Salary) from employees;

Output

Conclusion

Understanding how to use GROUP BY and ORDER BY statements is important for sorting the data and results. Whether we want to organize our data in ascending or descending order then we have to use the ORDER BY clause and if we want to organize multiple results under one group then use GROUP BY Clause. Both clauses can be used together to group and then sort the aggregated data. Mastery of these clauses will enhance our ability to query and analyse data effectively.