Page 178 - Informatics_Practices_Fliipbook_Class12
P. 178
5. AVG(): AVG() function yields the average value of the specified attribute.
To find the average salary of all employees, the query will be:
SELECT AVG(Salary) AS Average_Salary
FROM EMPLOYEE;
Execution of the above query will produce the output shown in Table 4.35.
Average_Salary
79500.0000
Table 4.35 Average salary of employees rounded to 4 decimal places
Note that the results of applying the AVG() function will look more elegant if the average salary is shown up to two
places of decimal. For this purpose, SQL provides a ROUND() function, which rounds a number to a specified number
of decimal places. In the following query, ROUND() is used to round the average salary in each department to two
decimal places.
SELECT ROUND(AVG(Salary),2) AS Average_Salary
FROM EMPLOYEE;
Execution of the above query will produce the output shown in Table 4.36.
Average_Salary
79500.00
Table 4.36 Average salary of employees rounded to two decimal places
4.4.20 GROUP BY CLAUSE
GROUP BY clause groups the tuples based on value(s) of the specified attribute(s)
The GROUP BY clause returns summary data based on one or more groupings. The grouping may involve one or
more attributes of the table. For example, we can use the GROUP BY clause to count the number of employees in
each department of the EMPLOYEE table, or to find the total salary of each department in the EMPLOYEE table. The
GROUP BY clause can also be used with aggregate functions to generate database summary reports. The syntax for
GROUP BY clause is given below:
SELECT attribute1, attribute2, ..., aggregate_function (attributeN)
FROM table_name
[WHERE conditions]
GROUP BY attribute1, attribute2, ...
[ORDER BY attribute [ ASC | DESC ]];
Query: To display the minimum, maximum, and average salary (rounded to two decimal places) of each department
from the EMPLOYEE table.
SELECT Dept_No, MIN(Salary) AS MinSalary,
MAX(Salary) AS MaxSalary,
ROUND(AVG(Salary),2) AS AverageSalary
FROM EMPLOYEE
GROUP BY Dept_No;
In the above statement, the ROUND() is used to round the average salary in each department to two decimal places.
Execution of the above query will produce the output as given in Table 4.37.
164 Touchpad Informatics Practices-XII

