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)
-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
-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
-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
-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
This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages: