Online Test Banks
Score higher
See Online Test Banks
eLearning
Learning anything is easy
Browse Online Courses
Mobile Apps
Learning on the go
Explore Mobile Apps
Dummies Store
Shop for books and more
Start Shopping

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.

COUNT

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 ;

MAX

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.

MIN

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.

SUM

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;

AVG

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.

  • Add a Comment
  • Print
  • Share
blog comments powered by Disqus
Advertisement

Inside Dummies.com

Dummies.com Sweepstakes

Win $500. Easy.