asked on

query group

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

_agx_

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

Shawn

ASKER

that does most of it but I still need the total logins...not just total logins per person.

Shawn

ASKER

If the total logins for Bob is 5 and Sue is 4 then I also need to output 9.

ASKER CERTIFIED SOLUTION

_agx_

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

_agx_

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

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

Shawn

ASKER

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.

Shawn

ASKER

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#

<cfset affilliateTotal = affilliateTotal + TotalLogins>
</CFOUTPUT>

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

_agx_

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>

Shawn

ASKER

thanks again agx!

Shawn

ASKER

ah right, went too fast. thx

_agx_

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.

Shawn

ASKER

I'm getting the results needed now. Total overall and total per affiliate.

_agx_

Ok, I guess you're using two variables: one for overall total and one for per affiliate total. So the numbers add up right :)

Shawn

ASKER

yep. that's it. :)

_agx_

Great. Just wanted to clarify it for future searchers of the archives ;-)