Solved

query group

Posted on 2011-03-22
15
238 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Lease-to-own eliminates the expenditure of hardware replacement and allows you to pay off the server over time. Usually, this is much cheaper than leasing servers. Think of lease-to-own as credit without interest.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

679 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