Oracle Aggregate Functions - oracle tutorial - sql tutorial



What is Aggregate Functions in oracle ?

  • Aggregate functions return a single value based on groups of rows, rather than single value for each row.
  •  aggregate functions types
  • The important Aggregate functions are :
    • Avg
    • Sum
    • Max
    • Min
    • Count
    • Stddev
    • Variance
Oracle sql count functions

Oracle sql count functions

 output select in aggregate functions
    • Here we are showing the table “wikitechy_char” with its fields before the AGGREGATE FUNCTION has been executed.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

AVG:

  • Returns the average value of expression.

Example:

SELECT AVG (Salary) FROM wikitechy_char;   
click below button to copy the code. By - oracle tutorial - team
 average in aggregate functions
    • AVG(Salary) function is being used to show the average of salary from the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the AVG(Salary) function as 26000.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

SUM:

  • Returns the sum value of expression.

Example:

SELECT SUM (Salary) FROM wikitechy_char;   
click below button to copy the code. By - oracle tutorial - team
 sum in aggregate functions
    • SUM (Salary) function is being used to show the sum of salary from the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the SUM(Salary) function as 130000.

MAX:

  • Returns maximum value of expression.

Example:

SELECT MAX (Salary) FROM wikitechy_char; 
click below button to copy the code. By - oracle tutorial - team
 maximum in aggregate functions
    • MAX (Salary) function is being used to show the maximum salary from the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the MAX(Salary) function as 30000.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

MIN:

  • Returns minimum value of expression.
      SELECT MIN (Salary) FROM wikitechy_char; 
click below button to copy the code. By - oracle tutorial - team

Example:

 minimum in aggregate functions
    • MIN (Salary) function is being used to show the minimum salary from the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the MIN(Salary) function as 20000.

COUNT:

  • Returns the number of rows in the query. If you specify expression then count ignore nulls.
  • If we specify the asterisk (*), this function returns all rows, including duplicates and nulls.
  • COUNT never returns null.

Example:

SELECT COUNT (*) FROM wikitechy_char;   
          SELECT COUNT (Salary) FROM wikitechy_char;  
click below button to copy the code. By - oracle tutorial - team
 count all in aggregate functions
    • COUNT (*) function is being used to show the number of rows in the table “wikitechy_char”.
    • Note: Asterisk (*), this function returns all rows,
    • Click on the RUN button to execute the query.
    • Here we can see the output of the COUNT(*) function as 5 .
    • COUNT (Salary) function is being used to show the number of rows in the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the COUNT(Salary) function as 5
 count salary in aggregate functions
    • COUNT (Salary) function is being used to show the number of rows in the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the COUNT(Salary) function as 5

STDDEV:

  • STDDEV returns sample standard deviation of expression, a set of numbers.

Example:

SELECT STDDEV (Salary) FROM wikitechy_char;  
click below button to copy the code. By - oracle tutorial - team
 standard deviation in aggregate functions
    • STDDEV (Salary) function is being used to show the standard deviation in the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the STDDEV(Salary) function as 4183.300.
oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , plsql

VARIANCE:

  • Variance returns the variance of expression.

Example:

         SELECT VARIANCE (Salary) FROM wikitechy_char;  
click below button to copy the code. By - oracle tutorial - team
 variance in aggregate functions
    • VARIANCE (Salary) function is being used to show the variance of salary in the table “wikitechy_char”.
    • Click on the RUN button to execute the query.
    • Here we can see the output of the VARIANCE (Salary) function as 1750000.

Oracle Exercise Aggregate Function Example

Oracle Exercise Aggregate Function Example

This tutorial provides an indepth knowledge on the following items such as oracle tutorial , sql tutorial , sql , pl sql tutorial , oracle , pl sql , mysql tutorial , sql tutorial for beginners , learn sql , oracle database tutorial , sql query tutorial , oracle dba tutorial , plsql tutorial , oracle tutorial pdf , oracle pl sql tutorial , oracle sql tutorial , sql tutorial point , oracle tutorial for beginners , learn oracle online free , learn oracle online , learning pl sql programming , learn sql online for free , sql learning online , dba oracle tutorial , oracle sql tutorial advanced , oracle 11g dba tutorial with examples , oracle online learning , oracle learning online , how to learn pl sql , sql coding tutorial , sql learning websites , sql basic learning

Related Searches to Oracle Aggregate Functions