Mục Lục

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

It is applied to group rows with same values.

It sorts the columns in either ascending or descending order.

It could be allowed in the create view statement.

It is not allowed to create view statement.

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.

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.

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.

It controls the presentation of the row

It controls the presentation of the column.

We can use the aggregate function in the Group by.

Here it’s not mandatory to use the aggregate function in the Order by.