In addition to putting fieldnames in the select statement, there are a variety of functions that can be placed there as well. These include COUNT(),SUM(),MIN(),MAX(),AVG(). For example:

SELECT COUNT(BOXNO),SUM(NETWEIGHT)
FROM production
WHERE PRODUCT="010"

When using any of these functions, keep in mind that the result returned is read only. If you do not check the read only box, you will get an error when trying to run the query.

Often you want a result that counts or groups based on certain field. For example, to produce a list of all farms with boxes together with the number of boxes they have produced try the following query:

SELECT FARM,COUNT(BOXNO),MIN(NETWEIGHT),MAX(NETWEIGHT)
FROM production
GROUP BY FARM

To accomplish this, you must use the GROUP BY clause. This clause instructs the interpreter to group together matching values in the named field.

In addition, you can apply conditions on which members of the group to include with the HAVING clause. For example, you want a list of all farms and their total box counts that have produced more than 100,000 lbs. Try this query:

SELECT FARM,COUNT(BOXNO),SUM(NETWEIGHT)
FROM production
WHERE datecode>"01/01/2001"
GROUP BY FARM
HAVING SUM(NETWEIGHT)>100000

Note that a query might return nothing if no matches are found for the criteria. This is called an empty result set.

The other function of an SQL command is to modify the data in a table. Again there are 3 basic clauses to perform an update:

1. UPDATE table-specifies which table to update.
2. SET field="value"-specifies which field(s) to update and what the new value(s) should be.
3. WHERE condition-specifies which records the change should be applied to.

For example, to take all the boxes from a packing slip and change the customer would require the following SQL command:

UPDATE PRODUCTION
SET CUSTOMER="0040"
WHERE SLIPID="17753"