SQL AVG, COUNT, MAX, MIN, SUM Functions

SQL AVG Function

SQL AVG function return average value of expression.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax

AVG( expression )

Example

Consider following emp_salary is our example table to find average salary using SQL AVG function.

SQL> SELECT * from emp_salary;

       NUM NAME                          SALARY
---------- ------------------------- ----------
         2 Max Miller                      2830
         3 Beccaa Moss                     2175
         4 Paul Singh                      5405
         5 Ken Myer                        3390
         6 Jack Evans                      3870

SQL> SELECT AVG(salary) "AVG SALARY" FROM emp_salary;

AVG SALARY
----------
      3534

Run it...   »


SQL COUNT Function

SQL COUNT function return the number of rows in a SELECT statement.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax

    COUNT( * )
    COUNT( [ DISTINCT | ALL ], expression )

Example

Consider following emp_salary is our example table to count total number of rows, count distinct value of column. Using SQL COUNT function.

SQL> SELECT * from emp_salary;

       NUM NAME                          SALARY
---------- ------------------------- ----------
         2 Max Miller                      2830
         3 Beccaa Moss                     2175
         4 Paul Singh                      5405
         5 Ken Myer                        3390
         6 Jack Evans                      3870
         6 Jack Evans                      3870
         5 Ken Myer                        3390

7 rows selected.

SQL> SELECT COUNT(*) FROM emp_salary;

  COUNT(*)
----------
         7

SQL> SELECT COUNT(name) FROM emp_salary;

COUNT(NAME)
-----------
          7

SQL> SELECT COUNT(DISTINCT name) FROM emp_salary;

COUNT(DISTINCTNAME)
-------------------
                  5

Run it...   »

SQL MAX Function

SQL MAX function return maximum value of expression.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax

MAX( expression )

Example

Consider following emp_salary is our example table to find maximum salary using SQL MAX function.

SQL> SELECT * from emp_salary;

       NUM NAME                          SALARY
---------- ------------------------- ----------
         2 Max Miller                      2830
         3 Beccaa Moss                     2175
         4 Paul Singh                      5405
         5 Ken Myer                        3390
         6 Jack Evans                      3870

SQL> SELECT MAX(salary) "MAX SALARY" FROM emp_salary;

MAX SALARY
----------
      5405

Run it...   »


SQL MIN Function

SQL MIN function return maximum value of expression.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax

MIN( expression )

Example

Consider following emp_salary is our example table to find minimum salary using SQL MIN function.

SQL> SELECT * from emp_salary;

       NUM NAME                          SALARY
---------- ------------------------- ----------
         2 Max Miller                      2830
         3 Beccaa Moss                     2175
         4 Paul Singh                      5405
         5 Ken Myer                        3390
         6 Jack Evans                      3870

SQL> SELECT MIN(salary) "MIN SALARY" FROM emp_salary;

MAX SALARY
----------
      2175

Run it...   »


SQL SUM Function

SQL SUM function return the sum of expression.

Supported Oracle SQL Version

  • Oracle 8i
  • Oracle 9i
  • Oracle 10g
  • Oracle 11g
  • Oracle 12c

Syntax

SUM( expression )

Example

Consider following emp_salary is our example table to find summation salary using SQL SUM function.

SQL> SELECT * from emp_salary;

       NUM NAME                          SALARY
---------- ------------------------- ----------
         2 Max Miller                      2830
         3 Beccaa Moss                     2175
         4 Paul Singh                      5405
         5 Ken Myer                        3390
         6 Jack Evans                      3870

SQL> SELECT SUM(salary) "SUM SALARY" FROM emp_salary;

SUM SALARY
----------
     20070

Run it...   »