Solved

query group

Posted on 2011-03-22
15
235 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
Meet the world's only “Transparent Cloud™” from Superb Internet Corporation. Now, you can experience firsthand a cloud platform that consistently outperforms Amazon Web Services (AWS), IBM’s Softlayer, and Microsoft’s Azure when it comes to CPU and …
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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