SQL tutorial-Aggregate functions

Aggregate functions

SQL aggregate functions allow us to answer the statistical inquiries about a set of data. For example, the users of your sale database system might want know the mean of the sales per year or the maximum and minimum sales of a quarter. The common SQL aggregate functions are AVG, MAX, MIN, COUNT and SUM.

-AVG Function: This function is used to calculate the average or the arithmetic mean of data values in a field of a table.
Example: Calculate the average of quantity of products in the TblProduct table below. Its syntax is AVG(field-name)

TblProduct table:

FROM TblProduct;

Result:

-MAX Function: The MAX function is used to find the maximum value among the data value of a field of a table. Its syntax is MAX(field-name).

Example: Find the maximum price of products in the TblProduct.

SELECT MAX(UnitPrice) AS Max_price

FROM TblProducts;

Result:

-MIN Function: This function is able to calculate the minimum value of the data value in field of a table. Its syntax is MIN(field-name).

Example: Find the minumum quantity of products in the TblProduct.

SELECT MIN(Qty) AS Min_qty

FROM TblProducts;

Result:

-Count Function: This function can count the number of records(rows) in a table. To count all rows in the table although some data in rows are missed, you can use COUNT(*). If you want to count only the rows which contain data you can use COUNT(fieldname) syntax.

Example: Count the products that are in the category id of Cat002.

SELECT COUNT(ProID) AS Countrows

FROM TblProducts

WHERE CatID='Cat002';

Result:

-SUM Function: This function is used to calculate the total of data values of a field of table. Its syntax is SUM(field-name).

Example: calculate the total quantity of the products in the TblProduct.

SELECT SUM(Qty) AS Totalqty

FROM TblProduct;

Result: