How to use aggregate SQL Select statements

Web developer can use special aggregate SQL functions which work with a set of values but return only a single value. For example, Web developer can use use an aggregate function to count the number of records in a table or to calculate the average price of a product:

     – Avg(fieldname) – Calculates the average of all values in a given numeric field.
     -Sum(fieldname) – Calculates the sum of all values in a given numeric field.
     -Min(fieldname) or Max(fieldname) – Finds the minimum or maximum value in a number field.
     -Count(*) – Returns the number of rows in the result set.
     -Count(DISTINCT fieldname) – Returns the number of unique (and non-null) rows in the result set for the specified field.

 
For example, here’s a query that returns a single value—the number of records in the Vendors table:
 

SELECT COUNT(*) FROM Vendors

 
And here’s how Web developer could calculate the total quantity of all sales by adding together the qty field in each record:

 

SELECT SUM(qty) FROM Sales