asked on

Help with SUM function

Please help me with this SUM function with a GROUP. I am trying to add the 'ArticleReviewCreditsClaimed'. I have it displaying with the article name and its credit value, but I cannot get the credits to total. Please help.

<cfset TempIDs = 0>
<CFSET Total = 0>

<cfquery name="getEvals" datasource="#DataTables#">
Select * FROM ArticleEvalQueue
Where ArticleQueueStatus = 0
</cfquery>

<cfoutput query="getEvals">
<cfset TempIDs = listappend("#TempIDs#","#ArticleQueueID#")>
</cfoutput>

<cfquery name="getProgram" datasource="#DataTables#">
SELECT ArticleEvalQueue.*, Articles.ArticleName, Articles.ArticleCMECredit, ArticleEvals.*, Articles.ArticleID,
SUM(ArticleEvaluations.ArticleReviewCreditsClaimed) AS Total, Visitors.*
FROM ArticleEvalQueue INNER JOIN
ArticleEvals ON ArticleEvalQueue.ArticleQueueID = ArticleEvals.ArticleQueueID INNER JOIN
ArticleEvaluations ON ArticleEvals.ArticleEvalID = ArticleEvaluations.ArticleReviewID INNER JOIN
Articles ON ArticleEvaluations.ArticleID = Articles.ArticleID INNER JOIN
Visitors ON ArticleEvalQueue.ArticleQueueVisitorID = Visitors.VisitorID
WHERE ArticleEvals.ArticleQueueID in(#TempIDs#)
Order by ArticleEvals.ArticleQueueID ASC
</cfquery>

<table width="100%">
<cfoutput query="getProgram" group="ArticleQueueID">
<tr>
<td>
<STRONG>#VisitorFirstName# #VisitorLastName#</STRONG>
<cfoutput>
<li>#ArticleName# (#ArticleID# - #ArticleQueueID# - #ArticleReviewCreditsClaimed#)</li>

</cfoutput>
</td>
</tr>
<tr><td>#total#</td></tr>
</cfoutput>
</table>

SOLUTION

James Rodgers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

James Rodgers

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

hart

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hart

Thnks for the points ;-)

anandkp

I wonder what has * got to do with group attribute in CFQUERY ???

Jeffmace : if ur query was giving out the correct output - all u had to do was sum up the total inside the inner output tag !

I am assuming this based on the previous question - something like this MIGHT have worked for u
[keeping ur query as it is]

<TABLE WIDTH="100%">
      <CFOUTPUT QUERY="getProgram" GROUP="ArticleQueueID">
            <TR>
                  <TD><STRONG>#VisitorFirstName# #VisitorLastName#</STRONG>
                  <CFSET THISTOTAL = 0>
                  <CFOUTPUT>
                        <LI>#ArticleName# (#ArticleID# - #ArticleQueueID# - #ArticleReviewCreditsClaimed#)</LI>
                        <CFSET THISTOTAL = (THISTOTAL + TOTAL)>
                  </CFOUTPUT>
                  </TD>
            </TR>
            <TR>
                  <TD>#ThisTotal#</TD>
            </TR>
      </CFOUTPUT>
</TABLE>

dont know if this is what u were looking for ... but thought wld give it a try !

K'Rgds
Anand

jeffmace

ASKER

Actually that is exactly what i wound up doing. At least i feel good about myself by figuring it out myself. :)

anandkp

Then i wonder - why u accepted something - that was not a required answere for u .... weird :)

Cos someone looking at this thread tomorrow may be misguided to think - thats the real answere ... whereas infact it is not !

jeffmace

ASKER

Well I just took the correct answer as to say that I could not use the SUM function in this instance which made me look for another solution. So now if someone comes to this thread they will see that they can not use the SUM function here and then will see your answer as another method. So its actually helps in 2 different ways