SQL Group Functions- AVG, COUNT, MAX, MIN and SUM

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);

Group Function Schema

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;

AVG function

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

AVG-Invalid Number

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

AVG-Missing Expression

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;

Count Group Function

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

count not null

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

count distinct

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.

max Missing Expression

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

max subject string

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

Max Marks Number

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

Min Subject String

Select min(marks) from sturesult

min Marks Number

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

min missing expression error

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

Sum Error Missing Expression

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

sum invalid number error

Select sum(marks) “Total” from sturesult

sum Total

You can read more about these functions here