CODEWITHSUNDEEP
htmlcoldfusion
learningUser
learningUser

Reputation: 13

How do I sum values in a column using a table?

I have a table that i get values from a query, I would want to get the total sum for every row. I have try using if statements something like 

 <cfif ECA GTE 0><td align="center">#ECA#</td>  <cfset totaleca+=ECA></CFIF>

but i dont get the correct sum. How would I get the sum for all the columns?

I made a http://jsfiddle.net/w2k3y3kz/2/

CODE: 

<table id="table-example-1"><caption>testing</caption>
   <thead><tr>
       <th colspan="1"></th>
       <th colspan="3">ECA</th>


    </tr><tr>

       <th></th>
       <th>open</th>



        </tr></thead>

<cfoutput query="getSubtotal">
    <cfset totaleca = 0>
<cfset totalsum = 0>
     </tr></thead><tbody><tr><td style="width:70px"; align="center">#numberformat(mid(getSubtotal.oaoperiod,5,2))#/#left(getSubtotal.oaoperiod,4)#  </td>
   <td align="center">#ECA#</td> 
     <td align="center">#ECAClose#</td> 
     <td align="center">#ECAcos#</td>   
    <td align="center">#NSA#</td>   
     <td align="center">#NSAClose#</td> 


    </tr>
    </tbody>

    </cfoutput>
    <cfoutput>
        <tfoot>


    <tr>
     <td></td>

    </tr>

      </cfoutput>
        </tfoot>
    </table>

Upvotes: 1

Views: 142

Answers (2)

Dan Bracuk
Dan Bracuk

Reputation: 20804

You can use array functions on query columns. The equivalent command for this:

<cfif ECA GTE 0><td align="center">#ECA#</td>  <cfset totaleca+=ECA></CFIF>

is

<cfset totaleca = ArraySum(getSubtotal['eca'])>

If you want to add the totals of more than one column, do something like this:

<cfstet totalSum = ArraySum(getSubtotal['eca']) 
                        + ArraySum(getSubtotal['ecaclose']) 
                        etc ... >

Upvotes: 4

Roul
Roul

Reputation: 965

I think you can refer to Ben Nadel's this Blog post for applying different array functions on a query object.

Example: If query object contains two columns Name and Mark, we have to calculate sum of all marks then we can use: Arraysum( q['mark'] )

Similarly, we can also use other array functions like: ArrayMax(q['Mark']), ArrayMin(q['Mark']), ArrayAvg(q['mark'])

Upvotes: 3

Related Questions