Introduction SQL

Basic SQL

SQL Constraints

Advance SQL

SQL Joins

SQL Function



SQL AVG, COUNT, MAX, MIN, SUM Function


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...