Group By, Having, and Ordered By Functions in SQL

The GROUP BY clause is used to group rows to compute group-statistics. It is to be noted that when the GROUP BY clause is present, then the SELECT clause may include only the columns that appear in the GROUP BY clause and aggregate functions.

In order to understand the GROUP BY Function let us consider the table PLACEMENT as shown in Table 4.5 which refers to the number students placed in different companies. The table PLACEMENT consists of three attributes (columns) which are company name, department name which refers to the curriculum stream and strength which refers to the number of students placed.

 

The syntax of GROUP BY command is: 

SELECT attribute name, aggregate function

FROM table name

GROUP BY attribute name;

Example: to find the total number of students placed in each branch is

HAVING Command

The HAVING command is used to select the group. In other words HAVING restricts the groups according to a specified condition. The syntax of HAVING command is:

SELECT attribute name, aggregate function

FROM table name

GROUP BY attribute name

HAVING condition

Example: to find the details of the department in which more than 90 students got placement

SORTING of Results

The SQL command ORDER BY is used to sort the result in ascending or descending order.The table used to understand ORDER BY command is BESTCRICKETER. The table BESTCRICKETER as shown in Table 4.6 gives the details of best batsman of the world. The attributes of the BESTCRICKETER are the name of the batsman, the country they belong to, and the number of centuries they scored

The syntax of ORDER BY command to arrange the result in ascending order is:

SELECT *

FROM table name

ORDER BY attribute name ASC / DSC;

Example:

Range Queries Using Between

The SQL has built-in command BETWEEN which is used to perform range queries.

Let us try to find the details of the batsman who has scored centuries greater than 20 and less than 30.