Aggregate Functions in Oracle
What are Aggregate Functions in Oracle:
The Aggregate functions in Oracle are used to perform the operations, calculations on rows and it returns a single value as a result. The aggregate functions are used with select clause and combined with Group By clause to perform calculations on groups of rows.
Following is the list of aggregate functions in Oracle:
1. COUNT Function():
Count() is used to display the number of rows or records stored in a table.
Syntax:
Select count(expression) from Tablename;
Forom following table total number of rows are 5 and it is displayed with count() below this data,
SQL> select * from student;
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 29 66
BCA02 Seeta BBA A 27 60
BCA03 Sakshi MCA C 23 67
BCA04 Soham BA C 23 67
BCA05 Sagar BBA C 29 56
SQL> select count(*) as Total_Rows from student;
TOTAL_ROWS
----------
5
Here count() is used to display the total number of records in the student table.
2. SUM() Function:
SUM() function is used to display the total sum of a numeric column.
Syntax:
Select SUM(column) from Tablename;
For Example:
SQL> select * from student;
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 29 66
BCA02 Seeta BBA A 27 60
BCA03 Sakshi MCA C 23 67
BCA04 Soham BA C 23 67
BCA05 Sagar BBA C 29 56
SQL> select sum(int_marks) as Total_internal_marks from student;
TOTAL_INTERNAL_MARKS
--------------------
131
Here in the above query the total sum of internal marks is displayed with SUM().
3. AVG() Function:
The AVG() function is used to calculate the average value of numeric column.
Syntax:
Select AVG(Column) From Tablename;
For Example,
From the student table select average external marks,
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 29 66
BCA02 Seeta BBA A 27 60
BCA03 Sakshi MCA C 23 67
BCA04 Soham BA C 23 67
BCA05 Sagar BBA C 29 56
SQL> select AVG(Ext_Marks) as Average_Internal_Marks from student;
AVERAGE_INTERNAL_MARKS
----------------------
63.2
4. MIN () Function:
The MIN() function is used to select the minimum value from table;
Syntax:
Select MIN(Column) From Tablename;
For Example,
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 29 66
BCA02 Seeta BBA A 27 60
BCA03 Sakshi MCA C 23 67
BCA04 Soham BA C 23 67
BCA05 Sagar BBA C 29 56
From above table select minimum internal marks,
SQL> select MIN(Int_marks) from student;
MIN(INT_MARKS)
--------------
23.
5. MAX() Function:
The MAX() function is used to select the maximum value from the numeric column.
Syntax:
Select MAX(Column) From Tablename;
For Example,
ROLLNO NAME CLASS DIV INT_MARKS EXT_MARKS
------ ------------ ---------- ---------- ---------- ----------
BCA01 Manisha BCA A 29 66
BCA02 Seeta BBA A 27 60
BCA03 Sakshi MCA C 23 67
BCA04 Soham BA C 23 67
BCA05 Sagar BBA C 29 56
From above table select maximum External marks
SQL> select MAX(Ext_Marks) From student;
MAX(EXT_MARKS)
--------------
67
6. Aggregate Function With Group By Function:
The aggregate function is used with Group By clause to gorup the data.
Syntax:
Select Column1, agregate_function(Column2) from Table Name
Group By Column1;
For Example,
SQL> select Class, Avg(Int_marks) from student
2 Group By Class;
CLASS AVG(INT_MARKS)
---------- --------------
BCA 29
BBA 28
MCA 23
BA 23
7. Aggregate Function with Having Clause:
The aggregate function is used with Group By clause and with Having Clause to give conditions on Group By Clause.
Syntax:
Select column1, aggregate_function(column2)
From Tablename
Group By Column1
Having condition;
For Example,
SQL> select Class, Avg(Int_marks) from student
2 Group By Class
3 Having avg(Int_marks)>10;
CLASS AVG(INT_MARKS)
---------- --------------
BCA 29
BBA 28
MCA 23
BA 23
In the above example condition is applied using a having clause to check avg internal marks greater than 10.
Another Example,
SQL> select Class, Avg(Int_marks) from student
2 Group By Class
3 Having avg(Int_marks)=15;
no rows selected
In the above example, the condition is applied to check average internal marks =10. No data found to satisfy the condition.
0 टिप्पण्या
कृपया तुमच्या प्रियजनांना लेख शेअर करा आणि तुमचा अभिप्राय जरूर नोंदवा. 🙏 🙏