Solved

Help with SUM function

Posted on 2003-12-11
8
201 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
  • 2
  • 2
  • 2
  • +1
8 Comments
 
LVL 25

Assisted Solution

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

Accepted Solution

by:
James Rodgers earned 250 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 250 total points
ID: 9925954
yes jesters right u cannot have a group by on *,
u need to specify the field names...


Regards
Hart
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
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 …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

770 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