SQL – Group By vs Order By
”;
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. |
”;