Question

Write the query for implementing the following functions: MAX(), MIN(), AVG() and COUNT().

Answer 1

Aggregate Functions

Aggregate Functions: Aggregate functions perform a calculation on a set of rows and return a single row. You can use aggregate functions as expressions only in the following clauses i.e., SELECT clause, HAVING clause. There are five aggregate functions: 

• AVG() – returns the average value.
• COUNT() – returns the number of values.
• MAX() – returns the maximum value.
• MIN() – returns the minimum value.
• SUM() – returns the sum of all or distinct values.

AVG(): The AVG() function allows you to calculate the average value of a numeric column.

syntax : AVG(column)

SELECT AVG(SALARY) "AVERAGE SAL" FROM empl;

Output : 

AVERAGE SAL
22000.0000

COUNT(): The COUNT function returns the total number of values in the specified field.

syntax : COUNT(expression)

SELECT COUNT(*) FROM empl;

Output : 

count(*)
6

MAX() : It returns the maximum value from the specified table field.

syntax : MAX(expression)

SELECT MAX(Salary) FROM empl;

Output : 

MAX(Salary)
30000

MIN(): The MIN function returns the minimum value in the specified table field.

syntax : MIN(expression)

SELECT MIN(Salary)FROM empl;

Output : 

MIN(Salary)
12000

SUM(): SUM function which returns the sum of all the values in the specified column. SUM works on numeric fields only. Null values are excluded from the result returned.

syntax : SUM(expression)

SELECT SUM(Salary) "Total Salary" from empl;

Output : 

Total Salary

132000

These functions are called aggregate functions because they operate on the aggregate of tuples. The result of an aggregate function is a single value.

---

For more Rajasthan Technical University CSE-IV Sem DBMS Lab Experiments CLICK HER
E