SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL what-are-single-row-and-multiple-row-subqueries">What are single row and multiple row subqueries?

GROUP BY vs ORDER BY

The GROUP BY clause and ORDER BY clause in SQL are used to arrange data obtained by SQL queries. The important difference between the GROUP BY clause and ORDER BY clause is that the GROUP BY clause is applied when we want to use an aggregate function in SQL query on more than one set of rows. In contrast, the ORDER BY clause is applied when we want to receive the data obtained by the query in the sorted order. Before moving towards comparison charts, we will first understand these SQL clauses.

GROUP BY Clause

The GROUP BY statement in SQL uses queries to arrange data within attribute values. Usually, we use the GROUP BY clause with the SELECT statement. We have to place the GROUP BY clause after the WHERE clause always. Additionally, it is also paced before the ORDER BY clause.

We can often use the GROUP BY clause with aggregate functions like SUM, AVG, MIN, MAX, and COUNT to produce output from the database. The query for the GROUP BY clause is grouped query, and it returns a single row for each grouped object.

Below is the Syntax to use the GROUP BY Clause in a SQL query

SELECT COLUMNNAME, FUNCTION(COLUMNNAME) FROM TABLENAME WHERE CONDITION GROUP BY COLUMNNAME;

Let’s understand the GROUP BY clause with the help of a few examples. Consider the following tables along with the given records.

Table: Employees

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDWORKING_HOURSGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE112F
1002VAIBHAVSHARMA60000NOIDAORACLE19M
1003NIKHILVANI50000JAIPURFMW210M
2001PRACHISHARMA55500CHANDIGARHORACLE110F
2002BHAVESHJAIN65500PUNEFMW212M
2003RUCHIKAJAIN50000MUMBAITESTING49F
3001PRANOTISHENDE55500PUNEJAVA39F
3002ANUJAWHERE50500JAIPURFMW29F
3003DEEPAMJAUHARI58500MUMBAIJAVA312M
4001RAJESHGOUD60500MUMBAITESTING410M

Example 1: Suppose we want to know employees sum salary in a particular city for the above data. To do this, we will execute the below query as follow:

SELECT CITY, SUM(SALARY) FROM EMPLOYEES GROUP BY CITY;

This query initially formed a result that has grouped the city. Next, the SUM function is performed on each group of cities, and finally, we will get the desired results as shown below:

GROUP BY vs ORDER BY

Example 2: Suppose we want to know employees' max salary in a particular department for the above data. To do this, we will execute the below query as follow:

SELECT DEPARTMENT, MAX(SALARY) FROM EMPLOYEES GROUP BY DEPARTMENT;

This query initially formed a result that has grouped the department. Next, the MAX function is performed on each group of the department, and finally, we will get the desired results as shown below:

GROUP BY vs ORDER BY

ORDER BY Clause

The GROUP BY clause and the ORDER BY clause could be used with the SELECT statement. In SQL queries, we used the ORDER BY clause to sort the records returned by the query in ascending or descending order. If we didn't mention the sorting order, the entire result would be sorted by default in ascending order.

Below is the Syntax to use the ORDER BY Clause in a SQL query

SELECT * FROM TABLENAME ORDER BY [ASC | DESC];

Let us understand the ORDER BY clause with the help of a few examples. Consider the following tables along with the given records.

Table: Employees

EMPLOYEEIDFIRST_NAMELAST_NAMESALARYCITYDEPARTMENTMANAGERIDWORKING_HOURSGENDER
1001VAIBHAVIMISHRA65000PUNEORACLE112F
1002VAIBHAVSHARMA60000NOIDAORACLE19M
1003NIKHILVANI50000JAIPURFMW210M
2001PRACHISHARMA55500CHANDIGARHORACLE110F
2002BHAVESHJAIN65500PUNEFMW212M
2003RUCHIKAJAIN50000MUMBAITESTING49F
3001PRANOTISHENDE55500PUNEJAVA39F
3002ANUJAWHERE50500JAIPURFMW29F
3003DEEPAMJAUHARI58500MUMBAIJAVA312M
4001RAJESHGOUD60500MUMBAITESTING410M

Example 1: Suppose we want to arrange the result in the sorted order, either ascending or descending, based on the first name column. In that case, we would like to use the ORDER BY clause to get the result. To do this, we will execute the below query as follow:

SELECT * FROM EMPLOYEES ORDER BY FIRST_NAME ASC;
GROUP BY vs ORDER BY

Key Difference between GROUP BY and ORDER BY

The following are the points that differentiate between the GROUP BY and ORDER BY clauses:

  • The GROUP BY statement in SQL is used to group the records based on a similar value in a particular column. On the other hand, the ORDER BY clause in SQL is used to sort the display results in ascending or descending order.
  • While performing a query using the GROUP BY clause in SQL query, it’s compulsory to use the aggregate function. On the other hand, while using the ORDER BY clause in SQ, it's not compulsory to use an aggregate function.
  • The GROUP BY clause is always placed in SQL query after the WHERE clause but before the ORDER BY clause. On the other hand, the ORDER BY clause is always placed after the GROUP BY clause.

Comparison Chart

SR.NOGROUP BYORDER BY
1The GROUP BY clause is used to group the rows with similar values.The ORDER BY clause is used to sort the result either in descending or ascending order
2It controls the presentation of rowsIt controls the presentation of columns
3THE GROUP BY clause is always placed before the ORDER BY clause.ORDER BY clause is always placed after the GROUP BY clause.
4It is compulsory to use an aggregate functionNot compulsory to use an aggregate function
5The attribute cannot be under the aggregate function in the GROUP BY statement. The attribute can be under the aggregate function in the ORDER BY statement.