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