Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Help with SUM function

Posted on 2003-12-11
8
Medium Priority
?
219 Views
Last Modified: 2013-12-24
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
Comment
Question by:jeffmace
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Assisted Solution

by:James Rodgers
James Rodgers earned 1000 total points
ID: 9923049
when you sum you need to do a group by,
0
 
LVL 25

Accepted Solution

by:
James Rodgers earned 1000 total points
ID: 9923129
when you sum you need to do a group by, in the SQL, but you cannot group on *
0
 
LVL 11

Assisted Solution

by:hart
hart earned 1000 total points
ID: 9925954
yes jesters right u cannot have a group by on *,
u need to specify the field names...


Regards
Hart
0
Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

 
LVL 11

Expert Comment

by:hart
ID: 9925986
Thnks for the points ;-)
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9926008
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
 

Author Comment

by:jeffmace
ID: 9926019
Actually that is exactly what i wound up doing.  At least i feel good about myself by figuring it out myself.  :)
0
 
LVL 17

Expert Comment

by:anandkp
ID: 9926068
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
 

Author Comment

by:jeffmace
ID: 9926082
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

Simplify Your Workload with One Tool

How do you combat today’s intelligent hacker while managing multiple domains and platforms? By simplifying your workload with one tool. With Lunarpages hosting through Plesk Onyx, you can:

Automate SSL generation and installation with two clicks
Experience total server control

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever sent email via ColdFusion and thought of tracking this mail to capture the exact date and time when the message was opened ?  If yes, then this article is for you ! First we need a table user_email with columns user_id , email , sub…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

604 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