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
Solved

query group

Posted on 2011-03-22
15
237 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
  • 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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Portable, direct connect server access

The ATEN CV211 connects a laptop directly to any server allowing you instant access to perform data maintenance and local operations, for quick troubleshooting, updating, service and repair.

Question has a verified solution.

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

Suggested Solutions

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…

860 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