Aggregate functions OR Aggregate operators in SQL

SQL provides seven built-in functions to facilitate query processing. The seven built-in functions are COUNT, MAX, MIN, SUM, AVG, STDDEV, and VARIANCE. 

For Group By, Having, and Ordered By Functions in SQL Check Here

1. COUNT Function

The built-in function returns the number of rows of the table.

The syntax of Count (*) function is:

SELECT COUNT (*)

FROM table name;

Example:

2. MAX, MIN, and AVG Aggregate Function

In order to understand MAX, MIN, and AVG aggregate function consider the relation CONSUMER PRODUCTS. The relation CONSUMER PRODUCTS has two attributes, the name of the product and the price associated with the product as shown in Table 4.4.

a) MAX Command

The MAX command stands for maximum value. The MAX command returns the maximum value of an attribute. The syntax of MAX command is:

SELECT MAX (attribute name)

FROM table name;

Example-1:To get the maximum price of the product, the SQL command

Exampe-2: To find the name of the product which has maximum price by using PROJECTION operation and the IN operator

b) MIN Command

·        The MIN command is used to return the minimum value of an attribute. The syntax of MIN command is same as MAX command.

Syntax of MIN Command is

SELECT MIN (attribute name)

FROM table name; 

Example-1: To find the minimum price of the product is 3,500 which are returned as the result.

Example-2: To know the name of the product which has minimum price, we can use IN operator

c) AVG Command

The AVG command is used to get the average value of an attribute.

The syntax of AVG command is:

SELECT AVG (attribute name)

FROM table name;

Example: to get the average price of the product

d) STDDEV Function

The STDDEV function is used to compute the standard deviation of the attribute values. The syntax of the standard deviation function is:

SELECT STDDEV (attribute name)

FROM table name;

Example: The STDDEV function applied to the relation CONSUMERPRODUCT

 

e) VARIANCE Function

The variance function is used to get the variance of the attribute values. 

The syntax of VARIANCE function is:

VARIANCE (attribute name)

FROM table name;