Skip to main content
A
Anonymous
April 13, 2009
Answered

Query of a query...

  • April 13, 2009
  • 1 reply
  • 484 views

I've generated a query that outputs several types of data, one of which is a dollar value.  At the end of the report, I want to be able to get the TOTAL of all those dollar fields.  But I don't know how.  Kinda like the old lady that has fallen down and can't get up.  Help if you can.  Brendan

    This topic has been closed for replies.
    Correct answer ilssac

    The easiest way is to sum the desired field as you are outputing the details, then show this sum total at the end.

    I.E.

    <cfset bFieldTotal = 0>
    <cfoutput query="myReportData">
      #aField#  #bField# #cField#<br/>
      <cfset bFieldTotal = bFieldTotal + bField>
    </cfoutput>
    <cfoutput>bFieldTota is #bFieldTotal#</cfoutput>

    But you could do a sum query of query if you really want to, I'm pretty sure query of query supports the sum group operation.

    <cfquery name="mySum" dbType="query">
      SELECT sum(bField) AS bFieldTotal
      FROM myReportData
    </cfquery>
    <cfoutput query="mySum">#mySum.bFieldTotal#</cfoutput>

    1 reply

    ilssac
    ilssacCorrect answer
    Inspiring
    April 13, 2009

    The easiest way is to sum the desired field as you are outputing the details, then show this sum total at the end.

    I.E.

    <cfset bFieldTotal = 0>
    <cfoutput query="myReportData">
      #aField#  #bField# #cField#<br/>
      <cfset bFieldTotal = bFieldTotal + bField>
    </cfoutput>
    <cfoutput>bFieldTota is #bFieldTotal#</cfoutput>

    But you could do a sum query of query if you really want to, I'm pretty sure query of query supports the sum group operation.

    <cfquery name="mySum" dbType="query">
      SELECT sum(bField) AS bFieldTotal
      FROM myReportData
    </cfquery>
    <cfoutput query="mySum">#mySum.bFieldTotal#</cfoutput>
    tclaremont
    tclaremont
    Inspiring
    April 13, 2009

    I always wind up using Ian's first suggestion, and Sum-ing as I go along. This allows me to use conditional logic inside the query loop if there is a chance of an exception that I do NOT want to include.

    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices