CFtips.net

Calculations in cfoutput query using Avg, Min, Max, Sum

Filed under ColdFusion

You can use the arraySum, arrayMin, arrayAvg, arrayMax functions to quickly do calculations in an output query without using sql. Wish I knew of this tag along time ago.  It would have eliminated some extras queries I have created.

<cfquery name="myquery" datasource=">
SELECT Name, Age
FROM Employees
</cfquery>

<table>
  <tr>
    <td>Name</td>
    <td>Age</td>
 </tr>
  <cfoutput query="myquery">
    <tr>
      <td>#myquery.Name#</td>
      <td>#myquery.Age#</td>
      </td>
    </tr>
  </cfoutput>
</table>

----------------------------------------------------------------------------------------

RESULT:

Oldest Age: 28
Youngest Age: 24
Avg Age: 25.5
Sum of Ages: 102

To determine what is the oldest age, we would use the

ArrayMax  <cfoutput>#ArrayMax(myquery["Age"])#</cfoutput>
Result: 28

To determine what is the Youngest age, we would use the

ArrayMin  <cfoutput>#ArrayMin(myquery["Age"])#</cfoutput>
Result: 24

To determine the avereage age, we would use the

ArrayAvg  <cfoutput>#ArrayAvg(myquery["Age"])#</cfoutput>
Result: 25.5

To determine the sum of all ages, we would use the

ArraySum  <cfoutput>#ArraySum(myquery["Age"])#</cfoutput>
Result: 102