SQL tutorial-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.
SELECT AVG(Qty) AS AvgQty
-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