The data stored in tables can be summarized using SQL Group Functions. The five group functions in SQL are- AVG, COUNT, MAX, MIN and SUM. All these functions return one numeric value that is why these are known as group or aggregate functions. The function names are not case sensitive. You can write them in any case as you wish. So, let’s understand the syntax of these functions, what they return and which data type can be used with them.
The examples use the following schema. Create it before trying the examples.
Create Table StuResult
(StudentID varchar2(5),
Subject varchar2(15),
Marks number)
Insert into STuresult values (‘A1202′,’Data Analytics’,50);
Insert into STuresult values (‘A1202′,’Robotics’,34);
Insert into STuresult values (‘A1202′,’Data Structures’,23);
Insert into STuresult values (‘A1202′,’Web programing’,90);
Insert into STuresult values (‘A1202′,’IOT’,45);
Insert into STuresult values (‘A1202′,’Prac DBMS’,56);
Insert into STuresult values (‘A1202′,’Prac Web prog’,null);

SQL Group Functions
AVG
This function is used to find the average value for values stored in a column of a table.
The syntax of the function is
AVG(col-name)
col- name is the name of the column that stores numeric values like salary, marks, sold items, purchased items, or leaves availed.
Input Type– Numeric values only
Output Type– Numeric
Example
Select avg(marks) as “Average Marks” from stuResult;

Select avg(subject) as “Average sub” from stuResult; // AVG function gives error for string data type

Select avg(*) as “Average sub” from stuResult;// AVG function gives error a column is not specified

Count
This function is used to count the number of rows in table satisfying the condition if specified with WHERE Clause. If you want to count unique values in a column then use the keyword DISTINCT. The count function excludes all the null values while returning the result. The syntax of the function is
COUNT(*|[DISTINCT] col-name)
col- name is the name of the column. You can use either * for all columns or name of a specific column to count the values in that column.
Using DISTINCT keyword with a column name will return count of unique values in column excluding the null values.
Input Type– Any data type
Output Type– A positive whole Number
Example
Select count(*) from sturesult;

Select count(marks) from sturesult // Count function excludes the null value in Marks column from the result

Select count(distinct studentID) from sturesult // Count function returns count of unique values

MAX
This function is used to find the maximum value in a given column of a table satisfying condition if specified with WHERE Clause. The syntax of the function is
MAX( col-name)
col- name is the name of the column. The column data type can be anything-numeric, string or date.
Input Type– Any Data type
Output Type– Data type of the column specified
Example
Select max(*) as “Max All” from stuResult; \\ Max function gives error if specific column name is not passed as argument.

Select max(subject) as “Max Subject” from stuResult; \\ with character data

Select max(marks) as “Max marks” from stuResult; \\with numeric data

MIN
This function is used to find the minimum value in a given column of a table satisfying condition if specified with WHERE Clause. The syntax of the function is
MIN( col-name)
col- name is the name of the column. The column data type can be anything- numeric, string or date.
Input Type– Any Data type
Output Type– Data type of the column specified
Example
Select min(subject) from sturesult

Select min(marks) from sturesult

Select min(*) from sturesult // MIN function reports an error when no column is specified

SUM
This function is used to find the sum of values in a given column of a table satisfying condition if specified with the WHERE Clause. The syntax of the function is
SUM( col-name)
col- name is the name of the column. The column data type can only be numeric.
Input Type– Numeric Data Type
Output Type– Numeric Data Type
Example
Select sum(*) “Total” from sturesult \\ Sum Function gives error when no column is specified

select sum(subject) “Total” from sturesult \\ Sum Function gives error when non-numeric column is specified

Select sum(marks) “Total” from sturesult

You can read more about these functions here