Oracle Aggregate Functions - Count Sum Avg Min Max

In continuation of basics yet important topics of Oracle Database, with this post, I am trying to explain Oracle Aggregate Functions with Group By and Having clause. Oracle Aggregate functions operate on multiple rows and return a single value for each group. Oracle Aggregate functions also called Group Functions.

Example 1: COUNT function count number of row for each group. Count(*) and Count(1) are same, Oracle internally translate count(1) to count(*). When we pass a column in count function, it counts all the rows having non NULL values.

ngarg> select count(*), count(1), count(empno), count(mgr) from emp;
  COUNT(*)   COUNT(1) COUNT(EMPNO) COUNT(MGR)
---------- ---------- ------------ ----------
        14         14           14         13

Example 2: SUM, AVG, MIN and MAX functions calculate total, average, minimum and maximum values respectively.

ngarg> select sum(sal), avg(sal), min(sal), max(sal) from emp;

  SUM(SAL)     AVG(SAL)   MIN(SAL)   MAX(SAL)
----------   ---------- ---------- ----------
   29031.2   2073.65714     800.75    5000.15

Example 3: GROUP BY clause allows us to create group for doing aggregation. In this example we have added Group By clause on deptno, which creates groups, and aggregate values for each deptno. All the columns with out aggregate functions used in SELECT and ORDER BY must be part of GROUP BY clause.

ngarg> select deptno, count(*), sum(sal), avg(sal), min(sal), max(sal)
  2    from emp
  3    group by deptno;

    DEPTNO   COUNT(*)   SUM(SAL)   AVG(SAL)   MIN(SAL)   MAX(SAL)
---------- ---------- ---------- ---------- ---------- ----------
        30          6    9403.19 1567.19833     950.99     2850.3
        20          5   10877.41   2175.482     800.75     3000.6
        10          3     8750.6 2916.86667       1300    5000.15

Example 4: HAVING clause allows us to put filters on Aggregate function, we can not use them in WHERE clause. For example we want to get the departments where more than 3 employees are working.

ngarg> select deptno, count(*), sum(sal), avg(sal), min(sal), max(sal)
  2    from emp
  3    group by deptno
  4    having count(*) >= 4;

    DEPTNO   COUNT(*)   SUM(SAL)   AVG(SAL)   MIN(SAL)   MAX(SAL)
---------- ---------- ---------- ---------- ---------- ----------
        30          6    9403.19 1567.19833     950.99     2850.3
        20          5   10877.41   2175.482     800.75     3000.6

Example 5: At last, we can use aggregate functions with strings and dates also

ngarg> select deptno, count(ename), min(ename), max(ename)
  2  from emp
  3  group by deptno;

    DEPTNO COUNT(ENAME) MIN(ENAME) MAX(ENAME)
---------- ------------ ---------- ----------
        30            6 ALLEN      WARD
        20            5 ADAMS      SMITH
        10            3 CLARK      MILLER

ngarg> select count(hiredate), min(hiredate), max(hiredate)
  2  from emp
  3  group by deptno;

COUNT(HIREDATE) MIN(HIRED MAX(HIRED
--------------- --------- ---------
              6 20-FEB-81 03-DEC-81
              5 17-DEC-80 23-MAY-87
              3 09-JUN-81 23-JAN-82

Related Posts -
- SQL Interview Question Answers
- Oracle: Rank, Dense_Rank, Row_Number Analytic Functions
- ROUND, TRUNC, CEIL and FLOOR in Oracle Database
- Difference between MOD and REMAINDER function
- Order By and Null values in Oracle Database
- Oracle: Some Important Date Queries
- Playing With Truncate and Date in Oracle Database
- NOT IN clause with NULL is tricky in Oracle Database
- Rollup and Grouping Sets in Oracle Database