![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Database
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - Group By vs Order By
In SQL, we have two common clauses that help us sort the data: Group By and Order By.
A Group By clause sorts the data by grouping it based on columns that we have specified at the time of the query and is used with the aggregate functions. An Order By clause will allow us to organize the sets alphabetically or numerically and in ascending or descending order.
Group By Clause
The Group By clause defines the group that you might want to evaluate as a whole in some calculations. This clause allows us to group the identical data into one subset instead of listing each individual records.
The query for the Group By clause is a grouped query, and it returns a single row for each object; it is often used with aggregate functions like MIN(), MAX(), SUM(), AVG(), and COUNT().
The Group By clause is used with the SELECT statement, and it is placed after the WHERE clause or before the HAVING clause. If we use the Order By clause, the Group By clause should precede the Order By clause.
Syntax
Following is the syntax of the Group By clause −
SELECT column_name, aggregate_function() FROM table_name WHERE condition GROUP BY column_name;
In the above Syntax, the aggregate_function() and the WHERE clause are optional. WHERE clause can be used to pass the condition and the aggregate function() is one of the aggregate functions such as count, calculate average, sum etc.
Example
In the SQL query below, we are using the Group by clause to group the rows based on the age values from the customers table.
Assume we have created a table named Customers, which contains the personal details of customers including their name, age, address and salary etc.
CREATE TABLE CUSTOMERS ( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (3, 'kaushik', 23, 'Kota', 2000.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (6, 'Komal', 22, 'MP', 4500.00 ); INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );
The table will be created as −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 4500.00 | | 7 | Muffy | 24 | Indore | 10000.00 | +----+----------+-----+-----------+----------+
Following is the query to count age and grouped by age −
SELECT AGE, COUNT(AGE) AS NumberOfAge from customers GROUP BY AGE;
Output
When we run the above query, we get the two columns age and number of age. The number of ages represents how many ages have the same value.
+------+-------------+ | age | NumberOfAge | +------+-------------+ | 32 | 1 | | 23 | 2 | | 25 | 1 | | 27 | 1 | | 22 | 1 | | 24 | 1 | +------+-------------+
Order By Clause
Listing query results in a specific order is a normal requirement, which is why SQL provides an "Order By" clause. The Order By clause is used in the SQL queries to sort the data in either ascending or descending order.
The "Order by" clause goes at the very end of the SELECT statement, after the "WHERE" clause, and defines the column that will determine the sorted order. The sort order can be in ascending or descending order.
The "DESC" keyword allows us to display in descending order. Whereas, "ASC" keyword allows us to display in ascending order. If no keywords are provided, the column's data will be arranged in ascending order by default.
Syntax
Following is the syntax to sort the column value in ascending/descending order using the ORDER BY clause −
SELECT column_name FROM table_name ORDER BY ASC/DSC;
Example
In the following SQL query retrieves the ID and NAME, and uses the Order by clause to sort the NAME in ascending order from the customers table.
SELECT ID, NAME FROM customers ORDER BY NAME;
Output
When we run the above query, we get a small table with two columns: ID and NAME. The table is sorted by name.
+------+----------+ | ID | NAME | +------+----------+ | 7 | Aman | | 3 | Chaitali | | 4 | Hardik | | 2 | kaushik | | 5 | Komal | | 6 | Muffy | | 1 | Ramesh | +------+----------+
Example
In the following example, we are retrieving the NAME, calculating the AVG SALARY, and using the GROUP BY clause to group the table by NAME.
SELECT NAME, AVG(SALARY) FROM customers GROUP BY NAME;
Output
When we run the above query, we get the name and average salary. The average salary is the same as the actual salary because there are no two or more than two records with the same name. As a result, the average salary is the same as the actual salary, and the table is grouped by name. as shown in the table below.
+----------+-------------+ | NAME | AVG(SALARY) | +----------+-------------+ | Ramesh | 2000.0000 | | kaushik | 2000.0000 | | Chaitali | 6500.0000 | | Hardik | 8500.0000 | | Komal | 4500.0000 | | Muffy | 10000.0000 | | Aman | NULL | +----------+-------------+
Example
In the following example, we are retrieving, NAME, AGE, and SALARY and using the ORDER BY clause to sort the AGE in the ascending order.
SELECT NAME, AGE, SALARY FROM customers ORDER BY AGE;
Output
When we run the above query, we get the name, age, and salary, and all of the columns are sorted by age, which is in ascending order, as shown in the table below.
+----------+------+--------+ | NAME | AGE | SALARY | +----------+------+--------+ | Komal | 22 | 4500 | | kaushik | 23 | 2000 | | Aman | 23 | NULL | | Muffy | 24 | 10000 | | Chaitali | 25 | 6500 | | Hardik | 27 | 8500 | | Ramesh | 32 | 2000 | +----------+------+--------+
Group by vs Order by
Following table summarizes the differences between the Group By clause and Order by clause −
S.No. | Group By | Order By |
---|---|---|
1 | It is applied to group rows with same values. |
It sorts the columns in either ascending or descending order. |
2 | It could be allowed in the create view statement. |
It is not allowed to create view statement. |
3 | The attribute cannot be assigned to the aggregate function in the Group By statement. |
The attribute can be assigned to the aggregate function in the Order By statement. |
4 | It is always used before the Order by clause in the select statement. |
It is always used after the Group by clause in the select statement. |
5 | Here grouping is done based on the similarity among the row’s attribute value. |
Here, the result-set is sorted based on the column’s attribute value either ascending or descending order. |
6 | It controls the presentation of the row |
It controls the presentation of the column. |
7 | We can use the aggregate function in the Group by. |
Here it’s not mandatory to use the aggregate function in the Order by. |