代码之家 › 专栏 › 技术社区 › David Brierton

CF查询删除总计的空字符串结果

coldfusion-10 coldfusion

0

David Brierton · 技术社区 · 9 年前

我正在尝试修正我的查询,使我的总和列等于正确的数字。我试着换这条线 <cfset columnSum = ArraySum(allLocCode['locationCount'])> 到 <cfset columnSum = ArraySum(trim(allLocCode['locationCount']))> 但这是一个错误。我希望下面图片中的空字符串不计入总数,就像它不显示在表中一样。有没有其他方法可以为我的整个专栏完成这个修剪?

<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>

正确答案应反映334而不是340

1 回复 | 直到 9 年前

1

0

TRose 9 年前

根据要求,这里有一个相关代码隔离的答案:

<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>

如果你需要更多关于百分比的帮助,我建议你开始一个新的帖子。我从您的历史中看到,您已经有几个线程与此功能相关,随着它变得越来越复杂,它将有助于分离所有内容。