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.
- The important Aggregate functions are :
- Avg
- Sum
- Max
- Min
- Count
- Stddev
- Variance
![aggregate functions types](https://wikitechy.com/tutorials/oracle/img\oracle-images\types-of-aggregate-functions.png)
![Oracle sql count functions](https://wikitechy.com/tutorials/oracle/img\oracle-images\sql-count.png)
Oracle sql count functions
![output select in aggregate functions](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-select-output.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-average.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-sum.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-maximum.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-minimum.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-count-all.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-count-salary.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-standard-deviation.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-aggregate-function-variance.png)
- 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](https://wikitechy.com/tutorials/oracle/img\oracle-images\oracle-exercise-aggregate-function-having-max.gif)