Explain any three aggregate functions with examples.
Aggregate Functions are keywords in SQL used to manipulate values within columns for output purposes. A function is a command always used in conjunction with a column name or expression. There are several types of functions in SQL. An aggregate function is used to provide summarization information for an SQL statement, such as counts, totals, and averages.
MIN – returns the smallest value in a given column
MAX – returns the largest value in a given column
SUM – returns the sum of the numeric values in a given column
AVG – returns the average value of a given column
COUNT- returns the total number of values in a given column
COUNT(*) – returns the number of rows in a table
SQL Aggregate Functions: SQL Aggregate Functions operate on complete sets of data and return a single result. PointBase supports five Aggregate Functions: AVG, COUNT, MAX, MIN, and SUM.
AVG
The AVG Function returns the average value for the column when applied to a column containing numeric data. The following is the syntax for the AVG Function.
AVG (column_name)
Example : SELECT AVG(commission_rate) FROM sales_rep_tbl
COUNT
The COUNT Function returns the number of rows in a specified result set. The following syntax is one form of the COUNT Function:
COUNT(*)
Example : SELECT COUNT(*) FROM sales_rep_tbl
The second form of the COUNT Function returns the number of rows in a result set where the specified column has a distinct, non-NULL value. The following syntax is the second form of the COUNT Function.
COUNT(DISTINCT column_name)
MAX
The MAX Function returns the data item with the highest value for a column when applied to a column containing numeric data. If you apply the MAX Function to a CHARACTER value, it returns the last value in the sorted values for that column. The following syntax is for the MAX Function.
MAX(column_name)
Example : SELECT MAX(commission_rate) FROM sales_rep_tbl
MIN
The MIN Function returns the data item with the lowest value for a column when applied to a column containing numeric data. If you apply the MIN Function to a CHARACTER value, it returns the first value in the sorted values for that column. The following syntax is for the MIN Function.
MIN(column_name)
Example : SELECT MIN(commission_rate) FROM sales_rep_tbl
SUM
The SUM Function returns the sum of all values in the specified column. The result of the SUM Function has the same precision as the column on which it is operating. The following syntax is for the SUM Function.
SUM(column_name)
Example : SELECT SUM(ytd_sales) FROM sales_rep_tbl