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:

ProID ProName UnitPrice Qty CatID
P001Angkor 20$ 10 Cat001
P002Anchor 19$ 15 Cat002
P003ABC 50$ 8 Cat003

SELECT AVG(Qty) AS AvgQty

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(field-name).

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(field-name).

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(field-name).

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

SELECT SUM(Qty) AS Totalqty

FROM TblProduct;

Result:

Totalqty
33




Comments

Name:
Comment:
Enter the text:
CAPTCHA image

.........................................................................................................................Home | Forum | About | Contact
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.
Copyright @ 2011-2013 worldbestlearningcenter. All Rights Reserved.
Home Programming Web DB & App Questions Exercises Tips Programs FAQs Download About
Computer-Wbest
Popular Pages
You might like