Solved

How to display summary fields

Posted on 2011-03-17
3
270 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:pdd1lan
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 500 total points
ID: 35156528
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
 
LVL 8

Author Comment

by:pdd1lan
ID: 35156817
it works. thanks.
0
 
LVL 8

Author Closing Comment

by:pdd1lan
ID: 35156823
good job
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Hi. There are several upload tutorials using jquery and coldfusion. I found a very interesting one here Upload Your Files using Jquery & ColdFusion and Preview them (http://www.randhawaworld.com/) . I did keep the main js functions but made sever…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question