Cold Fusion Tutorial Part III

6. Aggregate Functions

Aggregate Functions are used to summarise the results from your queries. The most common use for this is to display the number of results returned from a search. To do this you must use the COUNT function.

Figure 7. Count Function
Select COUNT(*) as Friends
From Friends

The above will return the number of friends you have. Notice the asterisk in the SQL, this is used because we want to simply count the total number of records. The asterisk can be replaced with a column name to just count the number of records in a particular column.

There are more aggregate functions of course, they are all used in the same way, and are listed below:

Count() - Counts the number of results.
Sum() - Calculates the total of values returned.
Avg() - Calculates the average of values returned.
Min() - Calculates the smallest value, earliest date, or first entry alphabetically.
Max() - Calculates the largest value, latest date, or last entry alphabetically.

7. Sorting Results

When you run a select statement the results will be returned in the order they were entered into the table. This isn't always what you want, sometimes you may want to order the results by a set of criteria. To do this you use the Order by clause.

Figure 8. Ordering Results
Select Firstname, Lastname
From Friends
Order by Lastname

What the above will do is return a list of your friends name's ordered by their last name alphabetically. If you wanted to start with the Z's and work your way to the A's you would change it to read Order by Lastname DESC. DESC is short for descending and will reverse the order in which the records are listed.

But what if you want to order by more than one column? Lets assume you have a friend named John Smith and a friend named Peter Smith. They will be listed in the right place by their last name but whether or not John is above Peter is dependent on the order you entered them into the database.

To fix this you simply pass two column names in the Order clause.

Figure 9. Ordering by Two Columns
Select Firstname, Lastname
From Friends
Order by Lastname, Firstname

8. Limiting Results

Often times it is desirable to only display 10 records at a time, for instance most search engines work this way. To do this you add a few arguments to your query tag.

Figure 10. Limiting Results
<CFQUERY DATASOURCE = "Friends" Name = "Query1" Maxrows = "10" Startrow = "0">
..
..
..
</CFQUERY>

Note that the starting row is set at 0. Remember that computers start numbering at 0, not 1. So the first record in the database is record number 0.

Conclusion:

If you've made it this far then you should be able to make a database driven website using Cold Fusion quite easily. I have covered what is needed to achieve the most commonly desired results of a database driven website, but I have only scratched the surface. There is a lot more to Cold Fusion and relational databases that I couldn't cover in a short tutorial. If you would like to learn more I suggest Cold Fusion Web Application Construction Kit 4.0 by Ben Forta. It is without a doubt the most comprehensive book I own and it should be able to assist you in all your Cold Fusion endeavors.