Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

query group

Posted on 2011-03-22
15
Medium Priority
?
247 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
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

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 …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

718 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