Oracle SQL Aggregates Functions - Orapoint

SQL has many cool features and aggregate functions are definitely one of these features, actually works. While they are not specific to SQL, they are often used. They are part of the SELECT statement, and this allows us to combine all the benefits of SELECT (joining tables, filtering only rows and columns) with the power of these functions.

• COUNT returns number of values in specified column. (Does not eliminate)
• SUM returns sum of values in specified column. (numeric)
• AVG returns average of values in specified column. (numeric)
• MIN returns smallest value in specified column.
• MAX returns largest value in specified column.

Example of Count() Function

SELECT
COUNT
(*)
FROM
employees
WHERE
salary > 10000;

Example of Sum() Function

SELECT
     SUM(salary) AS sumsalary
FROM employees
WHERE job_id = 10;

Example of MIN(), MAX(), AVG() Function

SELECT MIN(salary) AS myMin ,
       MAX(salary) AS myMax ,
       AVG(salary) AS myAvg
FROM   Employees;

Example of Group by Function

SELECT   department_id,
         SUM(Salary)
FROM     Employees
GROUP BY department_id

Example of Having Function

SELECT   department_id,
         SUM(salary)
FROM     employees
GROUP BY department_id
HAVING   SUM(salary) >30000