CODEWITHSUNDEEP
mysqlsqlcoldfusioncfquery
BirdDog
BirdDog

Reputation: 3

How to access CFQUERY SUM value

I am trying to get a single numeric value from a <cfquery> that is performing a SUMPRODUCT calc. I know that the the following code works to produce the desired value (verified through <cfdump>). I just need to know how to get that value as something that I can further manipulate in a <cfset> and display in a <cfoutput>.

This is the code that I am using:

<cfquery datasource="db" name="mQry">
    SELECT 
        SUM(factors.wt * temp.dp) 
    FROM
        factors
            INNER JOIN
        temp ON factors.fpID = temp.fpID
    WHERE
        factors.fpID IS NOT NULL
        AND temp.fpID IS NOT NULL  
</cfquery>

<cfdump var="#mQry#">

Upvotes: 0

Views: 507

Answers (1)

Jack Pilowsky
Jack Pilowsky

Reputation: 2303

First of all, you need to name the column in your query using AS ColumnAlias

<cfquery datasource="db" name="mQry">
SELECT 
    SUM(factors.wt * temp.dp) AS SumProduct
FROM
    factors
        INNER JOIN
    temp ON factors.fpID = temp.fpID
WHERE
    factors.fpID IS NOT NULL
    AND temp.fpID IS NOT NULL  
</cfquery>

To set it to a variable:

<cfset myVariable = mQry.sumProduct>

To output it

<cfoutput>#mQry.sumProduct#</cfoutput>

Upvotes: 6

Related Questions