Group Totals and Details in One Database Query

Submitted by Rudy Limeback, [e-mail]

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:

1. dept.id

2. dept.descr

3. rowtype DESC

4. staffid

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.

Table 28.2. The Raw Output of the Data We'll Be Working With

Id	Descr	Row-type	Staff-count	Staffid	Fullname
Acctg	Accounting	T	1
Acctg	Accounting	D	0	JD	John Dough
HR	Human Resources	T	3
HR	Human Resources	D	0	FJ	Fred Jones
HR	Human Resources	D	0	MS	Mary Smith
HR	Human Resources	D	0	TT	Thom Thumb
IT	Information Technology	T	4
IT	Information Technology	D	0	BB	Bill Brown
IT	Information Technology	D	0	JC	Jeff Case
IT	Information Technology	D	0	MR	Mike Rophone
IT	Information Technology	D	0	RG	Rudy Green
Mktg	Marketing	T	2
Mktg	Marketing	D	0	JW	Jane White
Mktg	Marketing	D	0	ME	Mark Etting

Here's the grouped output:

The Accounting department has 1 staff member:

• JD John Dough

The Human Resources department has 3 staff members:

• FJ Fred Jones

• MS Mary Smith

• TT Thom Thumb

The Information Technology department has 4 staff members:

• BB Bill Brown

• JC Jeff Case

• MR Mike Rophone

• RG Rudy Green

The Marketing department has 2 staff members:

• JW Jane White

• ME Mark Etting

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 /> &nbsp; #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.