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(fieldname)
TblProduct table:
ProID  ProName  UnitPrice  Qty  CatID 
P001  Angkor  20$  10  Cat001 
P002  Anchor  19$  15  Cat002 
P003  ABC  50$  8  Cat003 
FROM TblProduct;
Result:
AvgQty 
11 
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(fieldname).
Example: Find the maximum price of products in the TblProduct.
SELECT MAX(UnitPrice) AS Max_price
FROM TblProducts;
Result:
Max_price 
50$ 
MIN Function: This function is able to calculate the minimum value of the data value in field of a table. Its syntax is MIN(fieldname).
Example: Find the minumum quantity of products in the TblProduct.
SELECT MIN(Qty) AS Min_qty
FROM TblProducts;
Result:
Min_qty 
8 
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:
Countrows 
1 
SUM Function: This function is used to calculate the total of data values of a field of table. Its syntax is SUM(fieldname).
Example: calculate the total quantity of the products in the TblProduct.
SELECT SUM(Qty) AS Totalqty
FROM TblProduct;
Result:
Totalqty 
33 


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.