How to Set SQL Table Functions
Sometimes the information you want to extract from a table in SQL doesn’t relate to individual rows but rather to sets of rows. SQL provides five set (or aggregate) functions to deal with such situations. These functions are COUNT, MAX, MIN, SUM, and AVG. Each function performs an action that draws data from a set of rows rather than from a single row.
The COUNT function returns the number of rows in the specified table. To count the number of precocious seniors in a high-school database, use the following statement:
SELECT COUNT (*) FROM STUDENT WHERE Grade = 12 AND Age <14 ;
Use the MAX function to return the maximum value that occurs in the specified column. Suppose you want to find the oldest student enrolled in your school. The following statement returns the appropriate row:
SELECT FirstName, LastName, Age FROM STUDENT WHERE Age = (SELECT MAX(Age) FROM STUDENT);
This statement returns all students whose ages are equal to the maximum age. That is, if the age of the oldest student is 23, this statement returns the first and last names and the age of all students who are 23 years old.
This query uses a subquery. The subquery SELECT MAX(Age) FROM STUDENT is embedded within the main query.
The MIN function works just like MAX except that MIN looks for the minimum value in the specified column rather than the maximum. To find the youngest student enrolled, you can use the following query:
SELECT FirstName, LastName, Age FROM STUDENT WHERE Age = (SELECT MIN(Age) FROM STUDENT);
This query returns all students whose age is equal to the age of the youngest student.
The SUM function adds up the values in a specified column. The column must be one of the numeric data types, and the value of the sum must be within the range of that type.
Thus, if the column is of type SMALLINT, the sum must be no larger than the upper limit of the SMALLINT data type. To find the total dollar value of all sales recorded in the database, use the SUM function as follows:
SELECT SUM(TotalSale) FROM INVOICE;
The AVG function returns the average of all the values in the specified column. As does the SUM function, AVG applies only to columns with a numeric data type. To find the value of the average sale, considering all transactions in the database, use the AVG function like this:
SELECT AVG(TotalSale) FROM INVOICE
Nulls have no value, so if any of the rows in the TotalSale column contain null values, those rows are ignored in the computation of the value of the average sale.