Need to get totals from certain rows.
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?
AdChoices