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