Need to get totals from certain rows.

[BreakawayPaul]

I have a database that I'm pulling some funding numbers out of.  There are columns for state, year, funding amount, and project #.

I need to display them in a table by year, then state, sort of like this:

1992

[ California | Route 152: Santa Clara Co. | HW12345 | $000,000 ]

[ California | Route 154: Santa Barbara Co. | HW54325 | $000,000 ]

etc

I have the following code: 

<cfquery name="by_year" datasource="byways_grants">
SELECT    project.funding_amt
        , project.proj_year
        , project.proj_name
        , 'SB-' & project.proj_year & '-' & project.state_code & '-' & project.st_proj_no AS project_no
        , state.name
FROM project
INNER JOIN state
        ON state.code = project.state_code
WHERE funding_amt > 0
ORDER BY proj_year, state.name, proj_name
</cfquery>
<cfoutput query="by_year" group="proj_year">
<h2>#proj_year#</h2>
<table class="datatable pc100">
<tr>
<th scope="col" width="12%">State</th>
<th scope="col">Name</th>
<th scope="col">Project##</th>
<th scope="col" width="12%">Funding Amount</th>
</tr>
<cfoutput>
<tr>
<td scope="row">#htmleditformat(name)#</td>
<td>#htmleditformat(proj_name)#</td>
<td>#htmleditformat(project_no)#</td>
<td align="right">#LSCurrencyFormat(funding_amt,"local")#</td>
</tr>
</cfoutput>
</table>
</cfoutput>

What I'm trying to do is at the end of each state (like after the bottom row for California, where Colorado starts), have a row for totals for that state.  After a break from coding things, my brain is a bit dusty.  Any ideas on how to insert a row for totals after each group of states?

[p_sim]

I did not test it. It might be like this:

<cfset prev_name = "" />

                    <cfoutput>

                              <tr>

                                        <td scope="row">#htmleditformat(name)#</td>

                                        <td>#htmleditformat(proj_name)#</td>

                                        <td>#htmleditformat(project_no)#</td>

                                        <td align="right">#LSCurrencyFormat(funding_amt,"local")#</td>

                              </tr>

                              <cfset curr_name = name />

                              <cfif NOT ( (prev_name EQ "") OR (prev_name EQ curr_name) )>

                                        <!--- display total --->

                                        <tr>

                                                  <td align="right" colspan="4">#LSCurrencyFormat(total,"local")#</td>

                                        </tr>

                                        <!--- reset total --->

                                        <cfset total = 0 />

                              </cfif>

                              <cfset total = total + funding_amt />

                              <cfset prev_name = curr_name />

                    </cfoutput>

[p_sim]

<cfoutput query="by_year" group="proj_year">

          <h2>#proj_year#</h2>

          <table class="datatable pc100">

                    <tr>

                              <th scope="col" width="12%">State</th>

                              <th scope="col">Name</th>

                              <th scope="col">Project##</th>

                              <th scope="col" width="12%">Funding Amount</th>

                    </tr>

                    <cfset prev_name = "" />

                    <cfoutput>

                              <tr>

                                        <td scope="row">#htmleditformat(name)#</td>

                                        <td>#htmleditformat(proj_name)#</td>

                                        <td>#htmleditformat(project_no)#</td>

                                        <td align="right">#LSCurrencyFormat(funding_amt,"local")#</td>

                              </tr>

                              <cfset curr_name = name />

                              <cfif NOT ( (prev_name EQ "") OR (prev_name EQ curr_name) )>

                                        <!--- display total --->

                                        <tr>

                                                  <td align="right" colspan="4">#LSCurrencyFormat(total,"local")#</td>

                                        </tr>

                                        <!--- reset total --->

                                        <cfset total = 0 />

                              </cfif>

                              <cfset total = total + funding_amt />

                              <cfset prev_name = curr_name />

                    </cfoutput>

          </table>

</cfoutput>

[BreakawayPaul]

That works, but it has the same problem that my attempt had.  It puts the total for the state after the first occurence of the next state.

i.e.

California	Route 88: Alpine Co.	SB-1992-CA-04	$4,800.00
California	Statewide: Plan, Design, & Develop State Program	SB-1992-CA-07	$109,200.00
California	Statewide: Tourist Interpretation	SB-1992-CA-06	$120,000.00
Colorado	Alpine Loop: Hinsdale County Turnouts & Improvements	SB-1992-CO-06	$96,136.00
$736,400.00

But I was able to tweak it to get this:

<cfoutput query="by_year" group="proj_year">

<h2>#proj_year#</h2>

<table class="datatable pc100">

<tr>

<th scope="col" width="12%">State</th>

<th scope="col">Name</th>

<th scope="col">Project##</th>

<th scope="col" width="12%">Funding Amount</th>

</tr>

<cfset prev_name = "">

<cfoutput>

<cfset curr_name = name />

<cfif NOT ( (prev_name EQ "") OR (prev_name EQ curr_name) )>

<!--- display total --->

<tr>

<td align="right" colspan="4">#LSCurrencyFormat(total,"local")#</td>

</tr>

<!--- reset total --->

<cfset total = 0 />

</cfif>

<tr>

<td scope="row">#htmleditformat(name)#</td>

<td>#htmleditformat(proj_name)#</td>

<td>#htmleditformat(project_no)#</td>

<td align="right">#LSCurrencyFormat(funding_amt,"local")#</td>

</tr>

<cfset total = total + funding_amt />

<cfset prev_name = curr_name />

</cfoutput>

</table>

</cfoutput>

Which works quite well.

Thanks for the help!  I was quite stuck!

[BreakawayPaul]

Actually, I just noticed that it misses the final total at the end of the table.  Fiddling with that now.

EDIT: Got the last one to show up, but it's at the beginning of the table for the next year.  ARGH! Maddening!

[BreakawayPaul]

I ended up going a different route altogether.  This one not only gives me the state totals, it gives me annual totals.

<cfoutput query="by_year" group="proj_year">

<h2>#proj_year#</h2>

<table class="datatable pc100">

<tr>

<th scope="col" width="12%">State</th>

<th scope="col">Name</th>

<th scope="col">Project##</th>

<th scope="col" width="12%">Funding Amount</th>

</tr>

<cfoutput group="name">

<cfoutput>

<tr>

<td scope="row">#htmleditformat(name)#</td>

<td>#htmleditformat(proj_name)#</td>

<td>#htmleditformat(project_no)#</td>

<td align="right">#LSCurrencyFormat(funding_amt,"local")#</td>

</tr>

<cfset total = total + funding_amt>

<cfset ytotal = ytotal + funding_amt>

</cfoutput>

<tr>

<td colspan="3" class="txtbold">Totals for #htmleditformat(name)#</td><td align="right" class="txtbold">#LSCurrencyFormat(total,"local")#</td>

</tr>

<cfset total = 0>

</cfoutput>

<tr>

<td colspan="3" class="txtbold">Totals for #htmleditformat(proj_year)#</td><td align="right" class="txtbold">#LSCurrencyFormat(ytotal,"local")#</td>

</tr>

</table>

<cfset ytotal = 0>

</cfoutput>