5 Replies Latest reply on Aug 21, 2013 6:53 AM by BreakawayPaul

    Need to get totals from certain rows.

    BreakawayPaul Level 2

      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?

        • 1. Re: Need to get totals from certain rows.
          p.sim Level 3

          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>

          • 2. Re: Need to get totals from certain rows.
            p.sim Level 3

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

            1 person found this helpful
            • 3. Re: Need to get totals from certain rows.
              BreakawayPaul Level 2

              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.

               

              CaliforniaRoute 88: Alpine Co.SB-1992-CA-04$4,800.00
              CaliforniaStatewide: Plan, Design, & Develop State ProgramSB-1992-CA-07$109,200.00
              CaliforniaStatewide: Tourist InterpretationSB-1992-CA-06$120,000.00
              ColoradoAlpine Loop: Hinsdale County Turnouts & ImprovementsSB-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!

              • 4. Re: Need to get totals from certain rows.
                BreakawayPaul Level 2

                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!

                • 5. Re: Need to get totals from certain rows.
                  BreakawayPaul Level 2

                  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>