Solved

Help with SUM function

Posted on 2003-12-11
8
196 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
 
LVL 11

Expert Comment

by:hart
ID: 9925986
Thnks for the points ;-)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Running Guacamole on port 80 5 160
Two nodes for updates and forwarding 8 51
LAMP problem identifier tool ? 9 110
Nameserver and MX Record 2 63
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…
Article by: kevp75
Hey folks, 'bout time for me to come around with a little tip. Thanks to IIS 7.5 Extensions and Microsoft (well... really Windows 8, and IIS 8 I guess...), we can now prime our Application Pools, when IIS starts. Now, though it would be nice t…
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now