Aggregate function

Like most relational database products, AntDB supports aggregation functions. Aggregate functions are used to calculate a single result for multiple input records. For example, here we can see the aggregation functions COUNT, SUM, AVG (average), MAX (maximum) and MIN (minimum) used to perform calculations on a set of records.

For example, we can use the following query to find the highest and lowest salaries in the emp table.

SELECT MAX(sal) highest_salary, MIN(sal) lowest_salary FROM emp;

HIGHEST_SALARY | LOWEST_SALARY 
---------------+-----------------
          5000 |          800
(1 row)

If we wanted to find the employee with the highest salary, we might try this.

SELECT ename FROM emp WHERE sal = MAX(sal);

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: SELECT ename FROM emp WHERE sal = MAX(sal);
                                          ^

This query does not run because the aggregation function MAX cannot be used in the WHERE clause. The reason for this restriction is that the rows used for the aggregation calculation are determined by the WHERE clause, so the WHERE clause must be executed before the aggregation function can be executed. But by using subqueries, we can reuse the query to get the desired result.

SELECT ename FROM emp WHERE sal = (SELECT MAX(sal) FROM emp);

 ENAME 
------------
 KING
(1 row)

A subquery is run independently and is used to find out the results of its own run outside the scope of the enclosing query.

Aggregate functions are very useful when used in combination with the GROUP BY clause in a query. For example, the following query obtains the highest salary within each department.

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno;

  DEPTNO | MAX(SAL)  
---------+-----------
      10 |      5000
      30 |      2850
      20 |      3000
(3 rows)

This query produces one output row for each department. The result of each aggregation function is calculated over the records of the matched department. We can filter these grouped rows by using the HAVING clause.

SELECT deptno, MAX(sal) FROM emp GROUP BY deptno HAVING AVG(sal) > 2000;

  DEPTNO | MAX(SAL)  
---------+-----------
      10 |      5000
      20 |      3000
(2 rows)

This query gives the same results for those departments with an average salary greater than 2000.

This last query is used to get the employee whose job duties in each department are analysts, whose department has an average salary greater than 2000, and who has the most salary.

SELECT deptno, MAX(sal) FROM emp WHERE job = 'ANALYST' GROUP BY deptno HAVING AVG(sal) >
2000;

  DEPTNO | MAX(SAL)  
---------+-----------
      20 |      3000
(1 row)

There is a subtle distinction between the WHERE and HAVING clauses. The WHERE clause filters out the eligible rows before the grouping and aggregation functions are run. The HAVING clause, on the other hand, filters out the eligible rows after the rows have been grouped and the aggregation function has been calculated on each group.

So in the previous example, only the case where the job responsibility is that of an analyst is considered. In this subset of the output, the employees are grouped by department and only those records in the group with the job title Analyst and an average salary greater than 2000 appear in the final results. Only the group with department number 20, which has a maximum salary of 3000, is eligible in the final result.

Update Operations

We can use the UPDATE command to update the value of a column in an existing record. For example, the following command shows the results before and after each employee with a manager position received a ten percent raise.

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 ENAME | SAL  
-------+------
 JONES | 2975
 BLAKE | 2850
 CLARK | 2450
(3 rows)

UPDATE emp SET sal = sal * 1.1 WHERE job = 'MANAGER';

SELECT ename, sal FROM emp WHERE job = 'MANAGER';

 ENAME |  SAL   
-------+--------
 JONES | 3272.5
 BLAKE |   3135
 CLARK |   2695
(3 rows)

Delete Operations

The DELETE command is used to delete rows from a table. For example, the following command displays the results before and after deleting all employees with department number 20.

SELECT ename, deptno FROM emp;

 ENAME  | DEPTNO 
--------+--------
 SMITH  |     20
 ALLEN  |     30
 WARD   |     30
 MARTIN |     30
 SCOTT  |     20
 KING   |     10
 TURNER |     30
 ADAMS  |     20
 JAMES  |     30
 FORD   |     20
 MILLER |     10
 JONES  |     20
 BLAKE  |     30
 CLARK  |     10
(14 rows)

DELETE FROM emp WHERE deptno = 20;

SELECT ename, deptno FROM emp;

 ENAME  | DEPTNO 
--------+---------------
 ALLEN  |     30
 WARD   |     30
 MARTIN |     30
 KING   |     10
 TURNER |     30
 JAMES  |     30
 MILLER |     10
 BLAKE  |     30
 CLARK  |     10
(9 rows)

We should be especially careful when using the DELETE command without the WHERE clause. As follows:

DELETE FROM tablename; 

This statement will delete all records from the specified table. Before performing such an operation, the system will not ask for confirmation that such an operation is being performed.