Code360 powered by Coding Ninjas X Naukri.com. Code360 powered by Coding Ninjas X Naukri.com
Table of contents
1.
Introduction
2.
What is Order by Clause in SQL?
2.1.
Order By Syntax
2.2.
Explanation
2.3.
Example
2.4.
SQL
2.5.
Output
3.
What is Group by Clause in SQL?
3.1.
Group By Syntax
3.2.
Explanation
3.3.
Example
3.4.
SQL
3.5.
Output
4.
Difference Between Order by and Group by Clause in SQL
5.
Frequently Asked Questions
5.1.
Can the ORDER BY clause and the GROUP BY clause be used together in SQL?
5.2.
What is the main difference between ORDER BY and GROUP BY clauses?
5.3.
Is GROUP BY and ORDER BY same in SQL?
5.4.
Which is first GROUP BY or ORDER BY in SQL?
6.
Conclusion
Last Updated: Mar 27, 2024
Easy

Difference between GROUP BY and ORDER BY

Introduction

The GROUP BY statement is a powerful tool that facilitates data organization by grouping it according to specific columns specified in the query. This statement is commonly used alongside aggregate functions to summarize data effectively. On the other hand, the ORDER BY clause offers even greater control over result sets, allowing you to arrange them in either ascending or descending order, based on alphabetical or numerical values. 

Difference between GROUP BY and ORDER BY

SQL(Structured Query Language) is used for accessing and manipulating databases. Group By and Order By are both clauses of SQL. These are both used to organize data obtained by SQL queries. 

Before discussing the difference between GROUP BY and ORDER BY clauses, we will first know about these SQL clauses individually.

Also See, Basic SQL

What is Order by Clause in SQL?

ORDER BY clause sorts the result-set of data in ascending or descending order. By default, it sorts the data in ascending order. DESC keyword sorts the result-data set in descending order.

Order By Syntax

SELECT column1_, column2_….
FROM table_name
WHERE conditions_
ORDER BY column1_, column2_, .... ASC|DESC; 

Explanation

Here, SELECT column1_, column2_……. is the command that tells the SQL server to select column1_, column2_,.... from table “table_name” fulfilling the conditions in WHERE clause.

Example

In this example, we will create a table and use the order by clause on the Score column with the DESC keyword.

  • SQL

SQL

CREATE TABLE Students (
ID INT PRIMARY KEY,
Name VARCHAR(255),
Score INT
);

INSERT INTO Students (ID, Name, Score)
VALUES
(1, 'Alice', 85),
(2, 'Bob', 92),
(3, 'Charlie', 78),
(4, 'David', 95),
(5, 'Emily', 88);

SELECT Name, Score
FROM Students
ORDER BY Score DESC;

Output

 

output

The output is sorted in descending order based on the values of the score column.

Also see, Natural Join in SQL

What is Group by Clause in SQL?

As the name suggests, the GROUP BY clause groups the rows with the same attribute values. It uses aggregate functions like SUM, AVG, MIN, MAX, and COUNT. Here, the tuples(rows) are grouped based on similarities between attribute values.

Group By Syntax

SELECT column_name1, function_type(column_name2)
FROM table_name
WHERE conditions_
GROUP BY column_name1

Explanation

Here, SELECT column_name1, function_type(column_name2) selects column_name and applies the aggregate function “function_type” on column_name2 from table “table_name” fulfilling the conditions in WHERE clause and finally grouping them by column_name1.

Example

In this example, we will create a table of orders and use the group by clause on the CustomerName column along with the SUM aggregate function.

  • SQL

SQL

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerName VARCHAR(255),
OrderDate DATE,
OrderAmount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, CustomerName, OrderDate, OrderAmount)
VALUES
(1, 'Alice', '2023-09-01', 150.00),
(2, 'Bob', '2023-09-02', 200.50),
(3, 'Charlie', '2023-09-02', 75.25),
(4, 'Alice', '2023-09-03', 220.75),
(5, 'Emily', '2023-09-03', 180.00);

SELECT CustomerName, SUM(OrderAmount) AS TotalAmount
FROM Orders
GROUP BY CustomerName;

Output

output

The resultant table displays the total order amount for each customer.

Difference Between Order by and Group by Clause in SQL

S.No.

ORDER BY

GROUP BY

1

It sorts the result-set of data in ascending or descending order.

It groups the rows that have the same attribute values.

2

It handles the presentation of columns.

It handles the presentation of rows.

3

It is not allowed in CREATE VIEW statement.

It is allowed in CREATE VIEW statement.

4

In the SELECT statement, it is always used after the GROUP BY keyword.

In the SELECT statement, it is always used before the ORDER BY keyword.

5

In the ORDER BY clause, it is not compulsory to use aggregate functions.

In the GROUP BY clause, it is compulsory to use aggregate functions.

6

An attribute can be under the aggregate function in the ORDER BY clause.

An attribute cannot be under the aggregate function under the GROUP BY clause.

 

Must Read SQL Clauses

Frequently Asked Questions

Can the ORDER BY clause and the GROUP BY clause be used together in SQL?

Yes, but the placement of both clauses is vital. You must place the ORDER BY clause after the GROUP BY clause. And the GROUP BY clause must be placed after the WHERE clause.

What is the main difference between ORDER BY and GROUP BY clauses?

The ORDER BY clause sorts the data in ascending or descending order. Whereas the GROUP BY clause groups the tuples(rows) based on the similarities of columns. Using the aggregate function to use the GROUP BY clause is compulsory.

Is GROUP BY and ORDER BY same in SQL?

No, GROUP BY and ORDER BY are not the same. They serve different purposes. The GROUP BY clause is used to group rows that have the same values in one or more columns. On the other hand, the ORDER BY clause is used to sort the result set of a SQL query based on one or more columns.

Which is first GROUP BY or ORDER BY in SQL?

In SQL, the GROUP BY clause is executed before the ORDER BY clause. First, rows are grouped based on the specified columns in GROUP BY, and then the result set is sorted using ORDER BY.

Conclusion

In summary, both GROUP BY and ORDER BY are essential SQL clauses. GROUP BY helps organize data into groups, while ORDER BY sorts data based on specified columns. Understanding when to use each empowers efficient data management and querying in SQL.

Recommended Reading:

In this article, we started by discussing what ORDER BY and GROUP BY clauses are. Then, we learned the syntax of these clauses and discussed them with examples. Finally, we discussed the key differences between these two clauses.

Also, try Coding Ninjas Studio to practice programming problems problems for your complete interview preparation. Also, check out Top 100 SQL Problems and Top 30+ SQL Query Interview Questions and Answers to get hands-on experience with frequently asked interview questions and land your dream job.

Happy Coding!

Live masterclass