How to display summary fields

I can display my data as format below:

Area      Cost 1      Cost 2
A           2              2
A           1              3
B           0              1
B           1              1

How can I display like below?

Area      Cost 1      Cost 2
A           2              2
A           1              3
A Total   3               5

B           0              1
B           1              1
B Total   1              2

it won't work with the code below:

<CFQUERY name="AreaCount">
    SELECT Area,Cost1, Cost2
    FROM Table1
</CFQUERY>


<cfoutput>
      
   <table>
     <tr>
        <td>Area</td>
      <td>Cost 1</td>
      <td>Cost 2</td>
     </tr>

     <cfloop query="AreaCount">

       <tr>
         <td>#Area#</td>
       <td>#Cost1#</td>
       <td>#Cost2#</td>
       </tr>
     
        <!---here is where I want to show total of area--->

    <CFQUERY dbtype="query" name="GetTotal">
     SELECT Area,sum(cost1) as C1_Total, sum(cost2) as c2_Total
     FROM AreaCount
     where area=#area#
     GROUP BY Area
    </CFQUERY>

     <tr>
         <td>Total</td>
       <td>#GetTotal.C1_Total#</td>
       <td>#GetTotal.c2_Total#</td>
       </tr


     </cfloop>

   
     
   
   </table>

</cfoutput>

thanks for helps.
LVL 8
pdd1lanAsked:
Who is Participating?
 
gdemariaConnect With a Mentor Commented:
Use a group by in your cfouput loop and sub the values...
<CFQUERY name="AreaCount">
    SELECT Area,Cost1, Cost2
    FROM Table1
</CFQUERY>


      
   <table>
     <tr>
      <td>Area</td>
      <td>Cost 1</td>
      <td>Cost 2</td>
     </tr>

   <cfoutput query="AreaCount" group="area">
     <cfset variables.C1_subTotal = 0>
     <cfset variables.C2_subTotal = 0>

     <cfoutput>
     <tr>
       <td>#Area#</td>
       <td>#Cost1#</td>
       <td>#Cost2#</td>
     </tr>
      
       <cfset variables.C1_subTotal = variables.C1_subTotal + AreaCount.Cost1>
       <cfset variables.C2_subTotal = variables.C2_subTotal + AreaCount.Cost2>
     </cfoutput>
     <tr>
       <td>Total</td>
       <td>#variables.C1_subTotal#</td>
       <td>#variables.c2_subTotal#</td>
     </tr>
   </cfoutput>
   
   </table>

Open in new window

0
 
pdd1lanAuthor Commented:
it works. thanks.
0
 
pdd1lanAuthor Commented:
good job
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.