Submitted by Rudy Limeback, firstname.lastname@example.org
The challenge is to print groups of detail records but also precede each group with a line showing the number of detail records in the group.
To print groups, we are familiar with CFOUTPUT GROUP. We also know we can use RecordCount, but that gives only the total number of detail rows in the entire result set, not in the individual groups.
Your first thought might be to CFLOOP through the groups, build up an array of group detail record counts, CFOUTPUT the detail records, and in the GROUP logic, search through the array of saved counts and match on the group column but that's messy.
This solution embeds the group counts into the result set, using a "total" record that must sort first in each group. The CFOUTPUT GROUP logic will print the count, but it must skip over the "total" record so that it's not printed along with the "detail" records.
The query uses two subselects to obtain both totals and details, combining them with UNION ALL.
SELECT dept.id , dept.descr , 'T' as rowtype , count(*) as staffcount , '' as staffid , '' as fullname FROM dept INNER JOIN staff ON dept.id = staff.deptid GROUP BY dept.id, dept.descr UNION ALL SELECT dept.id , dept.descr , 'D' , 0 , staff.id , staff.fullname FROM dept INNER JOIN staff ON dept.id = staff.deptid ORDER BY 1, 2, 3 DESC, 5
The first subquery obtains the totals for each group. Each "total" record is identified by a 'T' in the rowtype column. The second subquery obtains the "detail" records, identified by 'D' in the rowtype column.
Notice that the ORDER BY is as follows:
The rowtype is sorted in descending order so that the 'T' record in each group precedes the 'D' records.
UNION ALL is specified instead of UNION to avoid the unnecessary sort to eliminate duplicates. (There cannot be any rows duplicated between subselects because of the rowtype column.)
Table 28.2 shows the raw data that the query produces. This is not what will be printed for the solution; it is shown just to illustrate the interleaving of "total" and "detail" rows.
Here's the grouped output:
The Accounting department has 1 staff member:
The Human Resources department has 3 staff members:
The Information Technology department has 4 staff members:
The Marketing department has 2 staff members:
Here's the CFOUTPUT that produced these results:
<cfoutput query="myquery" group="id"> <cfset firstrec = "y"> <p>The <b>#myquery.descr#</b> department has <strong>#myquery.staffcount#</strong> staff member<cfif myquery.staffcount GT 1>s</cfif>: <cfoutput> <cfif firstrec EQ "y"> <cfset firstrec = "n"> <cfelse> <br /> #myquery.staffid# #myquery.fullname# </cfif> </cfoutput> </p> </cfoutput>
The firstrec variable is set to "y" for each new group. This logic is conveniently controlled by the GROUP parameter. The nested CFOUTPUT, which prints the detail records, contains a CFIF test to skip over the first record so that it is not printed along with the details.