CODEWITHSUNDEEP
coldfusioncoldfusion-10
David Brierton
David Brierton

Reputation: 7397

CF query remove empty string results for total sum

I am trying to fix my query so that my total sum column will equal the correct number. I tried changing this line <cfset columnSum = ArraySum(allLocCode['locationCount'])> to <cfset columnSum = ArraySum(trim(allLocCode['locationCount']))> But it through an error. I want the empty string like in the picture below to not be counted for the total just like it does not show in the table. Is there another way to pull off this trim for my total column?

<cfset result = {} /> 
<cftry> 
    <cfquery datasource="#application.dsn#" name="GetLocationInfo">
        SELECT *
        FROM cl_checklists
    </cfquery>

    <cfcatch type="any"> 
        <cfset result.error = CFCATCH.message > 
        <cfset result.detail = CFCATCH.detail > 
    </cfcatch> 
</cftry> 

<table border="1" id="Checklist_Stats">
    <thead>
        <th><strong>Location</strong></th>
        <th><strong>Percent of Total Checklists</strong></th>
        <th><strong>Location Total</strong></th> 
    </thead>
    <tbody>
    <cfquery name="allLocCode" dbtype="query">
        SELECT DISTINCT trans_location, COUNT(*) AS locationCount FROM GetLocationInfo GROUP BY trans_location ORDER BY trans_location 
    </cfquery>
     <cfloop query="allLocCode">
      <cfset thisLocationName = trim(allLocCode.trans_location) />

      <cfquery name="allLocCodeForLocationQry" dbtype="query">
          SELECT trans_location,count(*) AS locCntr FROM GetLocationInfo WHERE trans_location='#thisLocationName#' GROUP BY trans_location ORDER BY trans_location
      </cfquery>
      <cfoutput query="allLocCodeForLocationQry">
      <tr>
        <td><strong>#thisLocationName#</strong></td>
        <td>#NumberFormat((allLocCodeForLocationQry.locCntr/allLocCode.locationCount) * 100, '9.99')#%</td>
        <td>#allLocCodeForLocationQry.locCntr#</td>
      </tr>
     </cfoutput>
     </cfloop>
        <cfset columnSum = ArraySum(allLocCode['locationCount'])>
     <tr>
      <td><strong>Total</strong></td>
      <td></td>
      <td><cfoutput>#columnSum#</cfoutput></td>
      <cfdump var="#allLocCode#">
      <cfdump var="#allLocCodeForLocationQry#">
      <cfdump var="#thisLocationName#">
    </tr>
    </tbody>
    <!--- Total of All Sum of each column --->
</table>

enter image description here

The correct answer should reflect 334 not 340

Upvotes: 0

Views: 296

Answers (1)

TRose
TRose

Reputation: 1738

As requested, here's an answer with the relevant code isolated:

<cfquery name="allLocCode" dbtype="query"> 
SELECT DISTINCT trans_location, COUNT(*) AS locationCount 
FROM GetLocationInfo 
WHERE trans_location is not null 
GROUP BY trans_location 
ORDER BY trans_location 
</cfquery>

If you need more help with the percentage stuff I'd recommend starting a new post. I see from your history you already have several threads kind of related to this feature, and as it gets more complicated it'll help to separate everything.

Upvotes: 0

Related Questions