In SQL, we have two commonly used clauses that help us to manipulate data; Group By clause and Order By clause.

A Group By clause is used to arrange the identical data/records into groups and the Order By clause is used to sort the data in ascending or descending order.

The SQL Group By Clause

Using the GROUP BY clause we can organize the data in a table into groups (based on a column) and perform required calculations on them.

This clause is often used with the aggregate functions such as MIN(), MAX(), SUM(), AVG(), and COUNT() etc.

It is often 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 SQL Group By clause −

SELECT column_name, aggregate_function() FROM table_name
WHERE condition GROUP BY column_name;

The aggregate_function() and the WHERE clause are optional in the above syntax.

Example

Assume we have created a table named CUSTOMERS that contains records of customers such as NAME, AGE, ADDRESS, and SALARY etc.., using the following CREATE statement −

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, we are inserting values into this table using the INSERT statement as follows −

INSERT INTO CUSTOMERS VALUES 
(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, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

The CUSTOMERS table will be created as −

In the SQL query below, we are using the Group by clause to group the rows based on their salaries from the CUSTOMERS table and counting the number of records in each group −

SELECT SALARY, COUNT(SALARY) from CUSTOMERS GROUP BY SALARY;

Output

When we execute the above query, the following result will be displayed −

The SQL Order By Clause

The ORDER BY clause is used to sort the query results. This clause is used at the end of a SELECT statement, following the WHERE, HAVING and GROUP BY clauses. We can sort the table column in ascending or descending order with the by specifying the sort order as ASC and DESC respectively. If we do not specify any order, it defaults to ascending order.

Syntax

Following is the syntax to sort the column value in ascending/descending order using the SQL ORDER BY clause −

SELECT column_name FROM table_name ORDER BY ASC/DSC;

Example

In the following query, we are retrieving the ID and NAME from the CUSTOMERS table and using the ORDER BY clause, we are sorting the names in ascending order.

SELECT ID, NAME FROM CUSTOMERS ORDER BY NAME;

Output

When we run the above query, we can see that the resultant table is sorted by name in ascending order.

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.

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

The table generated by the above query is as shown below −

Group by vs Order by

Following table summarizes the differences between the Group By clause and Order by clause −