how do you count counts?

[myatia]

Hi, all,

Does anyone know how to write a query to make a count of counts? I am currently tracking how many people log into my website, and I would like to find out how many people log in how many times. In other words, I want to know the number of people that log in 1 time, the number that log in 2 times, the number that log in 3 times, etc. I then want to group this data up by month.

I've tried the following, but I'm given a "Syntax error" message (no other details are listed):

SELECT LoginMonth, LoginMonthName, LoginCount, COUNT(id) AS AggLoginCount ;
FROM ( SELECT month(logindate) AS LoginMonth, cmonth(logindate) AS LoginMonthName, id, COUNT(id) AS LoginCount ;
	FROM logintrack ;
	GROUP BY LoginMonth, id );
ORDER BY LoginMonth, LoginCount ;
GROUP BY LoginMonth, id

FYI, I'm using VFP 6.0. If anyone has any ideas, I'd really appreciate it.

Thanks,

Misty

 

[mspratt]

Hi Misty,

I'm not certain, but I don't think VFP6 allows nested SELECT's. Do the inner select into a cursor and then do the outer select from the cursor.

Regards,

Mike

 

[Mike Lewis]

mike,

I don't think VFP6 allows nested SELECT's

That's correct. Misty would need VFP 9.0 for that.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[myatia]

Hi,

I got the following to work with cursors:

SELECT month(logindate) AS M, cmonth(logindate) AS MN, id, COUNT(id) AS LCount 
FROM logintrack 
GROUP BY M, id 
INTO CURSOR temp_logintrack

SELECT M, MN, LCount, COUNT(Lcount) AS AggLCount 
FROM temp_logintrack 
ORDER BY M, LCount 
GROUP BY M, MN, LCount

The problem is I'm trying to do this in ASP, which is having a problem with my temporary table. I tried the following but got an "File 'temp_logintrack.dbf' does not exist." error:

qry1 = "SELECT month(logindate) AS M, cmonth(logindate) AS MN, id, COUNT(id) AS LCount " & _
"FROM logintrack " & _
"GROUP BY M, id " & _
"INTO CURSOR temp_logintrack"

conn.execute(qry1)

qry2 = "SELECT M, MN, LCount, COUNT(Lcount) AS AggLCount " & _
"FROM temp_logintrack " & _
"ORDER BY M, LCount " & _
"GROUP BY M, MN, LCount"

set rs = conn.execute(qry2)

I can try posting on the ASP forum, but I thought I'd try it here, too. If anyone has any further advice, I'd greatly appreciate it!

Thanks for all your help,

Misty

 

[mspratt]

Hi Misty,

Your second select is closing your cursor. Put SELECT 0 between them.

Regards,

Mike

 

[myatia]

When I try conn.execute("SELECT 0"), I get a syntax error. I don't think ASP knows what SELECT 0 means.

 

[mspratt]

Hi Misty,

Instead of selecting into a cursor, select into a table. Even if it's closed the second select should be able to find it. When you don't need it any more, erase it.

Regards,

Mike

 

[myatia]

Hi, Mike,

I tried this, but I'm still getting the same ASP error:

-2147217865ASP Code=
Number=-2147217865
Source=
Filename=/admin/Other/login_reports.asp
Description=[Microsoft][ODBC Visual FoxPro Driver]File '#temp_logintrack.dbf' does not exist.

I tried several variations, like:

qry1 = "SELECT month(logindate) AS M, cmonth(logindate) AS MN, id, COUNT(id) AS LCount " & _
	"INTO TABLE #temp_logintrack " & _
	"FROM logintrack " & _
	"GROUP BY M, id "
			  
conn.execute(qry1)
		
qry2 = "SELECT M, MN, LCount, COUNT(Lcount) AS AggLCount " & _
	"FROM #temp_logintrack " & _
	"ORDER BY M, LCount " & _
	"GROUP BY M, MN, LCount" 
set rs = conn.execute(qry2)  '<<== ERROR HERE

and also:

qry = qry1 & "; " & qry2
set rs = conn.execute(qry)

I tried with and without the #. Is there an error in my code? Any advice would be appreciated.

Thanks,

Misty

 

[mspratt]

Hi Misty,

Is theis VB or VFP? the VFP continuation character is ; not & _ or is this the required syntax for SQL?

Regards,

Mike

 

[myatia]

I need to run the query in ASP (which uses VBScript) on a web page. I can get the results I want by running the two queries from the FoxPro command line, but not when I try to run it in ASP.

Misty