Aggregate functions

☞ Following are the topics to be covered : 

1. Aggregate Functions
2. SUM()
3. MAX()
4. MIN()
5. AVG()
6. COUNT()

AGGREGATE or GROUP or MULTIPLE ROW FUNCTIONS

☞ They operate on a group or aggregate of records/rows/tuples.

☞ They summarize the results of a query, and return a single value calculated from values in a column instead of providing the list of all of the rows.

☞ Following are the aggregate functions- SUM(), MAX(), MIN(), AVG(), COUNT()

☞ Many group functions accept the following options :

  1. DISTINCT : It is used to consider only distinct values of the argument expression.
  2. ALL : It is used to consider all values including all duplicates.

SUM() FUNCTION

☞ It is used to find the total value of any column or expression.

☞ It always takes argument of integer type only.

☞ It accepts entire range of values as an argument.

☞ Syntax :

SELECT SUM(<column_name>)
FROM <table_name>;

☞ Example :

SELECT SUM (fees)AS Sum
FROM Student;

MAX() FUNCTION

☞ It is used to find the highest value among the values in the given column or expression.

☞ Syntax :

SELECT MAX(<column_name>)
FROM <table_name>;

☞ Example :

SELECT MAX(fees)AS Maximum
FROM Student;

MIN() FUNCTION

☞ It is used to find the lowest value among the values in the given column or expression.

☞ Syntax :

SELECT MIN(<column_name>)
FROM <table_name>;

☞ Example :

SELECT MIN(fees)AS Minimum
FROM Student;

AVG() FUNCTION

☞ It is used to find the average value of any column or expression.

☞ Like SUM(), It always takes argument of integer type only.

☞ It also accepts entire range of values as an argument.

☞ Syntax :

SELECT AVG(<column_name>)
FROM <table_name>;

☞ Example :

SELECT AVG(fees)AS Average
FROM Student;

COUNT() FUNCTION

☞ It is used to count the number of values in a column.

☞ COUNT() takes only one argument, which can be any column name or an asterisk(*).

☞ When argument is a column name, COUNT() returns number of non-NULL values.

☞ When argument is asterisk(*), COUNT() counts the total number of records/rows/tuples satisfying the condition along with NULL values.

☞ Syntax :

SELECT COUNT(<column_name>)
FROM <table_name>;

☞ Example :

SELECT COUNT(fees)
FROM Student;

OR

SELECT COUNT(*)
FROM Student;