SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-42
Aggregate Functions in Predicates
Suppose that you want to find the sum of all salaries in the EMPLOYEE table. You can
specify this query:
SELECT SUM(SALARY)
FROM EMPLOYEE ;
The query returns this result:
(EXPR)
-----------
413210.00
--- 1 row(s) selected.
The query returns the sum of salaries of all employees in EMPLOYEE.
Now suppose that you want to find the name of the employee who makes the
maximum salary. You specify this query:
SELECT LAST_NAME, FIRST_NAME, SALARY
FROM EMPLOYEE
WHERE SALARY =
(SELECT MAX(SALARY) FROM EMPLOYEE) ;
The query returns this result:
LAST_NAME FIRST_NAME SALARY
--------- ---------- ------------
Nakagawa Etsuro 72000.00
--- 1 row(s) selected.
You can use the GROUP BY clause to group data for calculations. For example,
suppose that you want to find the sum of the salaries for employees in departments
6400 and 7690:
SELECT DEPT_NUM, SUM (SALARY)
FROM EMPLOYEE
WHERE DEPT_NUM IN (6400, 7690)
GROUP BY DEPT_NUM ;
The query returns this result:
DEPT_NUM (EXPR)
-------- ----------------
6400 130300.00
7690 81410.00
--- 2 row(s) selected.
In a query that selects both aggregate and nonaggregate columns, you must include
the GROUP BY clause on the nonaggregate columns. For more information about how
to use aggregate functions, see the SQL/MP Reference Manual.
The MAX and MIN functions support collations for character string arguments that
involve comparisons. For more information about collations, see the SQL/MP
Reference Manual.