Difference between order by and group by clause in SQL

ORDER BY clause is used to sort the result set either in ascending or descending order and GROUP BY the statement is used to group the rows that have the same value and it is used with aggregate functions like SUM(), AVG(), MAX(), etc. In this article, we are going to discuss both of these clauses in detail. Let’s start with Order by first.

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;

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

employee Table

Query

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

select * from employees ORDER BY salary;

Output

Table 1

Query

select * from employees ORDER BY salary DESC; 

Output

table 2

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;

Function_Name: Name of the aggregate function, for example:

SUM(), AVG(), COUNT() etc.

Table_Name: Name of the table.

Query

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

SELECT COUNT(Salary) from employee;

Output

table 3

Query

The AVG() function is used when we need to return the average value of the record.

SELECT AVG(Salary) from employees;

Output

table 4

Query

The SUM() function is used when we need to return the total sum of the record.

SELECT SUM(Salary) from employees;

Output

table 5

Difference Between ORDER BY and GROUP BY Clause

Conclusion

Understanding how to use GROUP BY and ORDER BY statements is important for sorting the data and results. Whether you want to organize your data in ascending or descending order then you have to use the ORDER BY clause and if you want to organize multiple results under one group then use GROUP BY Clause.