Coldfusion Math

Posted on 2007-09-30
Last Modified: 2013-12-16
I am oversimplifying so hopefully this will make sense:

If I have a query that returns to columns of numbers:

points     modifier
100        2
50          3
25          1

and I want to do the math such that

100 x 2
50 x 3
25 x 1

and then add up those totals (200 + 150 + 25) how can I do that?
Question by:BongSoo
    LVL 51

    Accepted Solution

    One option is to do the first calculation in your query sql:

       <cfquery name="yourQuery" datsource="....">
         SELECT   Points, Modifier, (Points * modifier) AS Total
         FROM     YourTable
         WHERE    ....

    Then use either a QoQ to calculate the grand total

       <cfquery name="getGrandTotal" dbtype="query">
         SELECT   SUM(Total) AS GrandTotal
         FROM     yourQuery

      <cfoutput><b>grand total=</b> #getGrandTotal.grandTotal#</cfoutput>

    ... Or calculate the grand total inside your output loop

       <cfset grandTotal = 0>
       <cfoutput query="yourQuery">
          points=#points# modifier=#modifier# total=#total#<br>
          <cfset grandTotal = grandTotal + total>
       <cfoutput><b>grand total=</b> #grandTotal#</cfoutput>
    LVL 16

    Author Comment

    Awesome! I was trying to figure out how to do the (Points * Modifier) AS Total, but I had it screwed up! Plus, your QofQ works better than what I was trying to do.

    Thanks for the quick response! A+!
    LVL 51

    Expert Comment

    Glad I could help :)  

    Featured Post

    Javascript: Crash Course

    Javascript is not only emerging as the most important language to know, it's flexibility is unique. With Javascript being deployed on the web, mobile and even the desktop this is the one language that all technologists need to know.

    Join & Write a Comment

    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…
    This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
    The purpose of this video is to demonstrate how to create a Printer Friendly PDF on a WordPress Page. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome Screenshot” Google Chrome Extension, and Log…
    The purpose of this video is to demonstrate how to properly insert a Vimeo Video into a WordPress site or Blog. This will be demonstrated using a Windows 8 PC. Go to your WordPress login page. This will look like the following:…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now