?
Solved

How to display summary fields

Posted on 2011-03-17
3
Medium Priority
?
274 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 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