Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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>
0
jeffmace
Asked:
jeffmace
  • 2
  • 2
  • 2
  • +1
3 Solutions
 
James RodgersWeb Applications DeveloperCommented:
when you sum you need to do a group by,
0
 
James RodgersWeb Applications DeveloperCommented:
when you sum you need to do a group by, in the SQL, but you cannot group on *
0
 
hartCommented:
yes jesters right u cannot have a group by on *,
u need to specify the field names...


Regards
Hart
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
hartCommented:
Thnks for the points ;-)
0
 
anandkpCommented:
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
0
 
jeffmaceAuthor Commented:
Actually that is exactly what i wound up doing.  At least i feel good about myself by figuring it out myself.  :)
0
 
anandkpCommented:
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 !
0
 
jeffmaceAuthor Commented:
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
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now