As part of learning the Oracle SQL tutorial, Here is good detail on the Aggregate functions in oracle
Aggregate functions in oracle/Group Functions
Group Functions unlike single value functions operate on the set of rows and return one row per group. The set of rows may be a whole table or the table split into groups
Syntax
Oracle server performed the following steps
- First, the rows are selected based on where clause
- Rows are grouped
- The group function is applied to each group
- The group that matches the criterion in the having clause are displayed
So the WHERE clause is evaluated first (restricts the query results), then the GROUP BY clause (groups the results of the WHERE), and then the HAVING clause (further restricts the results, by restricting the groups returned).
Important Points and Restrictions
- We can use distinct in the the function to consider only non-duplicate values. The default is ALL
- All the Group functions ignore Null values, we need to use NVL to consider them
- Oracle server implicility sorts the result in ascending order. if you want descending order , you need to use order by
- You can nest aggregate functions
Types of Aggregate Functions in Oracle include:
| AVG([Distinct/all] n) | Numeric data types only. The average value of the column n ignoring null values |
| COUNT({*/[Distinct/all]expr}) | It is only a group function that includes null values. It counts the number of rows in the select statement which satisfies the where clause. Count(*) includes all the null and duplicate values |
| MAX([Distinct/all] expr) | It can use with any data type. It gives the maximum value of expression ignoring null values |
| MIN([Distinct/all] expr) | It can use with any datatype. . It gives a minimum value of expression ignoring null values |
| STDDEV([Distinct/all] n) | Numeric data types only. It gives a standard deviation of n ignoring null values |
| SUM ([Distinct/all] n) | Numeric data types only and cannot have other arithmetic operators in the function. it provides the sum of n ignoring null values |
| VARIANCE([Distinct/all] n) | Numeric data types only. It gives variance of n ignoring null values |
Examples
Lets create an sample table and insert some data
CREATE TABLE "EMP"
( "EMPNO" NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0)
);
insert into emp values( 1000, 'Blake', 'MANAGER', 7839, to_date('1-5-2007','dd-mm-yyyy'), 2850, null, 10 );
insert into emp values( 1001, 'Clark', 'MANAGER', 7839, to_date('9-6-2008','dd-mm-yyyy'), 2450, null, 10 );
insert into emp values( 1002, 'Scott', 'ANALYST', 7566, to_date('9-6-2012','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 1003, 'TPM', 'ANALYST', 7566, to_date('9-6-2017','dd-mm-yyyy'), 3000, null, 20 );
insert into emp values( 1004, 'T1OM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 20 );
insert into emp values( 1005, 'TOM', 'ANALYST', 7567, to_date('9-7-2017','dd-mm-yyyy'), 4000, null, 30 );
Now lets take some examples based on this
Avg Function
The below statement gives the average salary in the Emp table
select avg(sal) from emp;
Count Function
The below statement gives the total count in the emp table
select count(*) from emp;
Min Function
select min(sal) from emp;
Max Function
select max(sal) from emp;
Sum function
select sum(sal) from emp;
I hope you like this post on aggregate functions in oracle
Related Articles
Analytic functions in oracle : Oracle Analytic functions compute an aggregate value based on group of rows by using over partition by oracle clause , they differ from aggregate functions
rank in oracle : RANK, DENSE_RANK and ROW_NUMBER are oracle analytical function which are used to rank rows in the group of rows called window
Lead function in oracle : Check out LAG function in Oracle & Lead function in Oracle, how to use them in analytic queries and how it works in Oracle sql
Top-N Queries in Oracle : Check out this page on exploring the various ways to achieve Top-N Queries in Oracle like oracle top 100, selecting top 10 rows in oracle
Auto Increment in Oracle : Check out how to use Auto Increment in Oracle before 12c version and with 12 and above version with detailed examples
group by oracle : GROUP BY oracle is used to group rows into a set of summary rows by values of single or multiple columns. Group by oracle order by
not a single-group group function : Check out a method to resolve ORA-00937: not a single-group group function. This error happens when you are using group functions
not a group by expression: Check out method to resolve ORA-00979: not a group by expression. This error happens when you are using group functions
https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqlj32654.html
