Skip to main content
K
kt03
Inspiring
November 29, 2011
Question

cfoutput group by

  • November 29, 2011
  • 1 reply
  • 857 views

Hi, my report is similar to the table below.  I need to to get the total of each state but my query didn't work, can you please help !!

StateCost
TX125
TX500
Totaltotal of TX
MO100
MO215
Totaltotal of Mo
CA500
Totaltotal of CA

<cfoutput query="getall">

     <cfset subtotal = 0 />

     <tr>

          <td>#state#</td>

          <td>#amount</td>

     </tr>

     <tr>

          <cfoutput group ="sate">

               <cfset subtotal = subtotal + amount />

          </cfoutput>

          <td>#subtotal#</td>

     </tr>

</cfoutput>

thanks

    This topic has been closed for replies.

    1 reply

    BreakawayPaul
    BreakawayPaul
    Inspiring
    November 30, 2011

    Try this:

    <cfparam name="total_cost" default="0">

    <cfquery name="qname" datasource="dsn">

    SELECT state

         , amount

    FROM tablename

    ORDER BY state

    </cfquery>

    <table border="1">

    <cfoutput query="qname" group="state">

       <cfoutput>

       <tr><td>#state#</td><td>#amount#</td></tr>

       <cfset total_cost = total_cost + amount>

       </cfoutput>

    <tr><td>Total #state#</td><td>#total_cost#</td></tr>

    <cfset total_cost = 0>

    </cfoutput>

    </table>

    You needed a second (blank) cfoutput nested within the first, with the group attrib on the first because the first one just loops through the states.  The second one loops through the amounts within each state.

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