Solved

Help with SUM function

Posted on 2003-12-11
8
206 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 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
Connect further...control easier

With the ATEN CE624, you can now enjoy a high-quality visual experience powered by HDBaseT technology and the convenience of a single Cat6 cable to transmit uncompressed video with zero latency and multi-streaming for dual-view applications where remote access is required.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
connection string question 2 73
Problem to go to page 12 111
question about access to website 2 87
SSL sertificate 5 75
Periodically we have to update or add SSL certificates for customers. Depending upon your hosting plan you may be responsible for the installation and/or key generation. In the wake of Heartbleed many sites were forced to re-key. We will concen…
Introduction This article explores the design of a cache system that can improve the performance of a web site or web application.  The assumption is that the web site has many more “read” operations than “write” operations (this is commonly the ca…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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