SQL Series...21

Functions

A function is a simple to moderately complex operation that the usual SQL commands don’t perform but that comes up often in practice. SQL provides functions that perform tasks that the application code in the host language (within which you embed your SQL statements) would otherwise need to per-form. SQL has two main categories of functions: set (or aggregate) functions and value functions.

Summarizing by using set functions

The set functions apply to sets of rows in a table rather than to a single row. These functions summarize some characteristic of the current set of rows. The set may include all the rows in the table or a subset of rows that a WHERE clause specifies. Programmers sometimes call set functions aggregate functions because these functions take information from multiple rows, process that information in some way, and deliver a single-row answer. That answer is an aggregation of the information in the rows making up the set.

To illustrate the use of the set functions, consider Table, a list of nutrition facts for 100 grams of certain selected foods.

Table                  Nutrition Facts for 100 Grams of Selected Foods

Food	Calories	Protein	Fat	Carbohydrate
		(Grams)	(Grams)	(Grams)
Almonds,	627	18.6	57.7	19.6
roasted
Asparagus	20	2.2	0.2	3.6
Bananas, raw	85	1.1	0.2	22.2
Beef,	219	27.4	11.3
lean hamburger
Chicken,	166	31.6	3.4
light meat
Opossum,	221	30.2	10.2
roasted

Food	Calories	Protein	Fat	Carbohydrate
		(Grams)	(Grams)	(Grams)
Pork, ham	394	21.9	33.3
Beans, lima	111	7.6	0.5	19.8
Cola	39			10.0
Bread, white	269	8.7	3.2	50.4
Bread,	243	10.5	3.0	47.7
whole wheat
Broccoli	26	3.1	0.3	4.5
Butter	716	0.6	81.0	0.4
Jelly beans	367		0.5	93.1
Peanut brittle	421	5.7	10.4	81.0

A database table named FOODS stores the information in Table. Blank fields contain the value NULL. The set functions COUNT, AVG, MAX, MIN, and SUM can tell you important facts about the data in this table.

COUNT

The COUNT function tells you how many rows are in the table or how many rows in the table meet certain conditions. The simplest usage of this function is as follows:

SELECT COUNT (*)

FROM FOODS ;

This function yields a result of 15, because it counts all rows in the FOODS table. The following statement produces the same result:

SELECT COUNT (Calories)

FROM FOODS ;

Because the Calories column in every row of the table has an entry, the count is the same. If a column contains nulls, however, the function doesn’t count the rows corresponding to those nulls.

The following statement returns a value of 11, because 4 of the 15 rows in the table contain nulls in the Carbohydrate column.

SELECT COUNT (Carbohydrate)

FROM FOODS ;

A field in a database table may contain a null value for a variety of reasons. A common reason for this is that the actual value is not known or not yet known. Or the value may be known but not yet entered. Sometimes, if a value is known to be zero, the data entry operator doesn’t bother entering anything in a field — leaving that field a null. This is not a good practice because zero is a definite value, and you can include it in computations. Null is not a defi-nite value, and SQL doesn’t include null values in computations.

You can also use the COUNT function, in combination with DISTINCT, to deter-mine how many distinct values exist in a column. Consider the following statement:

SELECT COUNT (DISTINCT Fat)

FROM FOODS ;

The answer that this statement returns is 12. You can see that a 100-gram serving of asparagus has the same fat content as 100 grams of bananas (0.2 grams) and that a 100-gram serving of lima beans has the same fat content as 100 grams of jelly beans (0.5 grams). Thus the table has a total of only 12 dis-tinct fat values.

AVG

The AVG function calculates and returns the average of the values in the spec-ified column. Of course, you can use the AVG function only on columns that contain numeric data, as in the following example:

SELECT AVG (Fat)

FROM FOODS ;

The result is 15.37. This number is so high primarily because of the presence of butter in the database. You may wonder what the average fat content may be if you didn’t include butter. To find out, you can add a WHERE clause to your statement, as follows:

SELECT AVG (Fat)

FROM FOODS

WHERE Food <> ‘Butter’ ;

The average fat value drops down to 10.32 grams per 100 grams of food.

MAX

The MAX function returns the maximum value found in the specified column. The following statement returns a value of 81 (the fat content in 100 grams of butter):

SELECT MAX (Fat)

FROM FOODS ;

MIN

The MIN function returns the minimum value found in the specified column. The following statement returns a value of 0.4, because the function doesn’t treat the nulls as zeros:

SELECT MIN (Carbohydrate)

FROM FOODS ;

SUM

The SUM function returns the sum of all the values found in the specified column. The following statement returns 3,924, which is the total caloric con-tent of all 15 foods:

SELECT SUM (Calories)

FROM FOODS ;