Khóa học miễn phí SQL – Group By vs Order By nhận dự án làm có lương

SQL – Group By vs Order By

Table of content


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 −

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 Hyderabad 4500.00
7 Muffy 24 Indore 10000.00

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 −

SALARY MAX(SALARY)
2000.00 2
1500.00 1
6500.00 1
8500.00 1
4500.00 1
10000.00 1

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.

ID NAME
4 Chaitali
5 Hardik
3 Kaushik
2 Khilan
6 Komal
7 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.000000
Khilan 1500.000000
Kaushik 2000.000000
Chaitali 6500.000000
Hardik 8500.000000
Komal 4500.000000
Muffy 10000.000000

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 −

NAME AGE SALARY
Komal 22 4500.00
Kaushik 23 2000.00
Muffy 24 10000.00
Khilan 25 1500.00
Chaitali 25 6500.00
Hardik 27 8500.00
Ramesh 32 2000.00

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 rows attribute value.

Here, the result-set is sorted based on the columns 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 its not mandatory to use the aggregate function in the Order by.

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc