cfoutput group by

[kt03]

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

State	Cost
TX	125
TX	500
Total	total of TX
MO	100
MO	215
Total	total of Mo
CA	500
Total	total 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

[BreakawayPaul]

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.