Solved

query group

Posted on 2011-03-22
15
241 Views
Last Modified: 2013-12-24
I am trying to get the total number of times logged in per person and the last login time...and can't remember how this is done.

Any ideas?
<!--- check out logins --->
<cfquery name="getAffiliateLogins"...>
SELECT     tblLoginSuccess.SuccessDate, tblLoginSuccess.KeyLinkSource, tblLoginSuccess.LoginMethod, tblAffiliates.FirstName, tblAffiliates.LastName, tblAffiliates.AffiliateID
FROM         tblLoginSuccess INNER JOIN
                      tblAffiliates ON tblLoginSuccess.AffiliateID = tblAffiliates.AffiliateID
</cfquery> 

<cfif getAffiliateLogins.recordcount NEQ 0>

<CFOUTPUT>
Total Affiliate logins: #getAffiliateLogins.RecordCount#<br>
</CFOUTPUT> 

<CFOUTPUT query="getAffiliateLogins" group="AffiliateID">
 
Name: #FirstName# #LastName#<br>
Number of logins:
Last Login:

</CFOUTPUT>  

</cfif>

Open in new window

0
Comment
Question by:Shawn
[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
  • 8
  • 7
15 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 35192686
If those are the only values you need, you can use sql (and get rid of the cfoutput group). Something like

SELECT     tblAffiliates.AffiliateID, tblAffiliates.FirstName, tblAffiliates.LastName,
                 COUNT(*)  AS TotalLogins,
                 MAX(tblLoginSuccess.SuccessDate) AS LastSucessDate
FROM         tblLoginSuccess INNER JOIN
                      tblAffiliates ON tblLoginSuccess.AffiliateID = tblAffiliates.AffiliateID
GROUP BY tblAffiliates.AffiliateID, tblAffiliates.FirstName, tblAffiliates.LastName
0
 
LVL 1

Author Comment

by:Shawn
ID: 35192731
that does most of it but I still need the total logins...not just total logins per person.
0
 
LVL 1

Author Comment

by:Shawn
ID: 35192749
If the total logins for Bob is 5 and Sue is 4 then I also need to output 9.
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 35192791
Total logins per ..  affiliateID, all affilliates overall, or both?


If it's per Affilliate, then group by Affilliate ID and track the totals in a variable.  Note: Be sure to ORDER the query by AffilliateID or the "group" won't work properly

<CFOUTPUT query="getAffiliateLogins" group="AffiliateID">
    <cfset affilliateTotal = 0>
    <CFOUTPUT>
        Name: #FirstName# #LastName#<br>
        Number of logins: #TotalLogins#
      Last Login:  #LastSucessDate#
     <!--- increment the total --->
     <cfset affilliateTotal = affilliateTotal  + TotalLogins>
    </CFOUTPUT>

    Total logins for affilliate = #affilliateTotal#
</CFOUTPUT>  



0
 
LVL 52

Expert Comment

by:_agx_
ID: 35192796

Total logins per ..  affiliateID, all affilliates overall, or both?

Oops... our posts clashed.  I see you answered that question above.
0
 
LVL 1

Author Comment

by:Shawn
ID: 35192837
not getting the overall total

results:
Name: Shawn Fawcett
Number of logins: 10 Last Login: 2011-03-18 16:56:03.727 Total logins for affilliate = 10
Name: Roy Sprenger
Number of logins: 1 Last Login: 2011-03-15 11:56:28.243 Total logins for affilliate = 1

should see 11 somewhere.
0
 
LVL 1

Author Comment

by:Shawn
ID: 35192869
got it. just put the total out of the loop

<cfset affilliateTotal = 0>
<CFOUTPUT query="getAffiliateLogins" group="AffiliateID">
   
    <CFOUTPUT>
        Name: #FirstName# #LastName#<br>
        Number of logins: #TotalLogins#
      Last Login:  #LastSucessDate#
     <!--- increment the total --->
     <cfset affilliateTotal = affilliateTotal  + TotalLogins>
    </CFOUTPUT>

   
</CFOUTPUT>  
Total logins for affilliate = <cfoutput>#affilliateTotal# </cfoutput>

0
 
LVL 52

Expert Comment

by:_agx_
ID: 35192894
Did you remember to ORDER the query results by AffilliateID?  The ORDER BY and "group" need to match for it to work properly

<cfquery ....>
SELECT .... etc....
ORDER tblAffiliates.AffiliateID
</cfquery>

<CFOUTPUT query="getAffiliateLogins" group="AffiliateID">
    .... etc.....
</CFOUPUT>
0
 
LVL 1

Author Closing Comment

by:Shawn
ID: 35192897
thanks again agx!
0
 
LVL 1

Author Comment

by:Shawn
ID: 35192908
ah right, went too fast. thx
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35192920
got it. just put the total out of the loop

Oh.. given your use of "group" I thought you wanted the total by affilliateID.  What you're doing is fine, but it'll produce the total overall, not for each affiliateID.  Which result do you want.
0
 
LVL 1

Author Comment

by:Shawn
ID: 35192963
I'm getting the results needed now. Total overall and total per affiliate.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35193225
Ok, I guess you're using two variables: one for overall total and one for per affiliate total. So the numbers add up right :)
0
 
LVL 1

Author Comment

by:Shawn
ID: 35193240
yep. that's it. :)
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35193329
Great.  Just wanted to clarify it for future searchers of the archives ;-)
0

Featured Post

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

Hi, Even though I have created this Tutorial on My personal Blog, Some people might not able to find my website, So here i am posting it again Today, from the topic it is very clear that i will be showing you here the very basic usage of how we …
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

624 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