Coldfusion counting looped query results from multiple datasources

Question

A query within a loop of a list containing multiple datasources produces records from each datasource. I would like to build a unique list of the divisions and count the number of times the division appears as it loops through the datasources.

This is working, but is there a better way using an array or struct?

<cfset divisions = "">

<!--- within query within loop --->

<cfif ListContains(divisions, "#SearchCompanyList.Division#")>
    <cfset variables['division#SearchCompanyList.Division#'] = variables['division#SearchCompanyList.Division#'] + 1>
<cfelse>
    <cfif Len(SearchCompanyList.Division) gt 0>
        <cfset divisions = ListAppend(divisions, "#SearchCompanyList.Division#")>
        <cfset variables['division#SearchCompanyList.Division#'] = 0>
    </cfif>
</cfif>

<!--- outside of query and loop --->

<cfloop list="#divisions#" index="div">
    <cfoutput>#div#: #Evaluate('division'&div)#</cfoutput>
</cfloop>

Answer 1

You are doing way too much variable indirection. Get rid of all the variables["#variablename#"] and the Evaluate(), all of that is unnecessary. This goes for the rest of your code, I bet you can simplify it all by a lot.

For your main issue, use a struct:

<cfset divisionCount = StructNew('casesensitive')>

<cfloop query="SearchCompanyList">
    <cfif StructKeyExists(divisionCount, Division)>
        <cfset divisionCount[Division]++>
    <cfelse>
        <cfset divisionCount[Division] = 1>
    </cfif>
</cfloop>

<cfoutput>
<cfloop array="#StructKeyArray(divisionCount)#" item="div">
    #encodeForHTML(div)#: #divisionCount[div]#<br>
</cfloop>
</cfoutput>

---

StructNew('casesensitive') is supported from CF2021. If you don't have that version yet, ColdFusion will upper-case the struct keys. Use a second struct to store the original division names, so that you can output them properly:

<cfset divisionCount = StructNew()>
<cfset divisionName = StructNew()>

<cfloop query="SearchCompanyList">
    <cfif StructKeyExists(divisionCount, Division)>
        <cfset divisionCount[Division]++>
    <cfelse>
        <cfset divisionCount[Division] = 1>
        <cfset divisionName[Division] = Division>
    </cfif>
</cfloop>

<cfoutput>
<cfloop array="#StructKeyArray(divisionCount)#" item="div">
    #encodeForHTML(divisionName[div])#: #divisionCount[div]#<br>
</cfloop>
</cfoutput>

---

For side-by-side counting of multiple columns, a reusable option with a little more advanced CF would be:

<cfscript>
function toGroupsBy(column) {
  return function (result, row) {
    var value = row[column];
    if (result.keyExists(value)) result[value][2]++; else result[value] = [value, 1];
    return result;
  };
}
</cfscript>

<cfset byDivision = SearchCompanyList.reduce(toGroupsBy('Division'), {})>
<cfset bySalesPerson = SearchCompanyList.reduce(toGroupsBy('SalesPerson'), {})>

<cfdump var="#byDivision#" label="Grouped by Division">
<cfdump var="#bySalesPerson#" label="Grouped by SalesPerson">

<cfoutput>
<cfloop array="#StructKeyArray(byDivision)#" item="div">
    #encodeForHTML(byDivision[div][1])#: #byDivision[div][2]#<br>
</cfloop>
</cfoutput>

---

For nested counting, I would actually use ColdFusion's native output grouping mechanism. That ordering the query properly is necessary for this to work. Note the nested <cfoutput> tags.

<cfquery name="SearchCompanyList" datasource="mydb">
  SELECT
    Division, SalesPerson
  FROM
    MyTable 
  ORDER BY
    Division, SalesPerson
</cfquery>

<cfoutput query="SearchCompanyList" group="Division">
  <cfset divCount = 0>
  <h3>#encodeForHTML(Division)#</h3>
  <ul>
    <cfoutput group="SalesPerson">
      <cfset persCount = 0>
      <cfoutput>
        <cfset persCount++>
        <cfset divCount++>
      </cfoutput>
      <li>#encodeForHTML(SalesPerson)#: #persCount#</li>
    </cfoutput>
    <li><b>Overall</b>: #divCount#</li>
  </ul>
</cfoutput>