Grouping Data with cfoutput

Grouping Data with <cfoutput>

<cfoutput> also can be used to group data according to a specific column for reporting purposes by using the group attribute. <cfoutput> does not sort data, so the data in the query object must already be appropriately ordered. After group is specified, the <cfoutput> statement loops only once per distinct value in the grouped column. A second, nested <cfoutput> statement can be used to loop over every record in a particular group, including the first record in the group:

 <!--- order results by department ---> <cfquery name="users" datasource="dsn"> SELECT EmpID, LastName, FirstName, Phone, DeptID FROM users ORDER BY DeptID </cfquery> 

This query returns data sorted by DeptID. There will be at least one user per retrieved DeptID, and possibly more too.

The following report uses the DeptID column in the group attribute of <cfoutput> to generate an HTML table:

 <table>   <cfoutput query="users" group="DeptID">     <tr>       <td colspan="3" bgcolor="##cccccc">#DeptID#</td>     </tr>     <cfoutput>       <tr>         <td>#LastName#, #FirstName#</td>         <td>#Phone#</td>       </tr>     </cfoutput>   </cfoutput> </table> 

The first <cfoutput> tag loops only once for each distinct value in the DeptID column, so if there were ten users in three departments, the outer <cfoutput> loop would be processed three times and the inner <cfoutput> loop would be processed a total of ten times.

NOTE

The group attribute of <cfoutput> is different from the SQL command GROUP BY. SQL's GROUP BY is used to provide a variety of aggregate functions on sets of records in a database query result. In comparison, group in <cfoutput> is used only to modify the output and looping behavior of the ColdFusion query object.

GROUP BY and aggregate functions are discussed in Chapter 46, "Aggregates."

Multiple levels of grouping are allowed as long as each has a unique group attribute and the data is sorted in the <cfquery> correctly. When grouping data, regardless of the number of levels used, group is used in every <cfoutput> except the innermost one.