Grouping Results

ColdFusion makes it easy to select data out of a database and display it on a page. As soon as you feel good about your success in crossing the divide from static to dynamic, someone will say "That's nice, can you group the list by author?". Whether you are asked to group things by author, make, brand, year or whatever, ColdFusion has you covered with its grouping capabilities.

To present things in groups, we first rely on SQL to group the data before it is presented to ColdFusion. This is done with a GROUP BY or ORDER BY clause.

GROUP BY SELECT Author, Title, Publisher
GROUP BY Publisher, Author, Title
ORDER BY SELECT Author, Title, Publisher
ORDER BY Publisher, Author, Title

ColdFusion will then display grouped data by using nested CFOUTPUT tags. In both cases the outter most CFOUTPUT will indicate the QUERY and each CFOUTPUT that is used to group will use a GROUP parameter except the innermost CFOUTPUT. The code below will group authors by publisher and then group books by author.

<CFOUTPUT Query="myquery" GROUP="Publisher">
   <br>~ #Title#

Macromedia Press
~CF WACK Third Edition
~Reality ColdFusion MX

Newbie Press
~Instant Messenger for Dummies
~Dominating the World with Software Patches

If you use a GROUP BY clause with a WHERE clause the GROUP BY clause most come after the WHERE clause. Think of this as the SQL engine finding all of the results that match the WHERE statement and then it groups the results.

If you use a GROUP BY clause with an ORDER BY, use the GROUP BY before the ORDER BY clause. The SQL engine will create the groups and then order the groups, this reduces the amount of ordering that is done by SQL since the number of groups is the same or less than the number of members of the groups.

More CFNewbie?com Articles