Set rs = Server.CreateObject("ADODB.Recordset")
StrSql = "SELECT M.M_COUNTRY, " & _
         "COUNT(M.MEMBER_ID) AS M_COUNT " & _
         "FROM FORUM_MEMBERS M " & _
         "GROUP BY M.M_COUNTRY " & _
         "ORDER BY COUNT(M.MEMBER_ID) DESC;"
rs.Open StrSql, my_Conn
do until rs.eof
strCountryName = Trim(rs("M_COUNTRY"))
 if strCountryName = "" then strCountryName = "Unknown"
 Response.write "<font face=""" & strDefaultFontFace & """ size=""" & strDefaultFontSize & """ color=""" & strDefaultFontColor & """>" & strCountryName & " - <i>" & rs("M_COUNT") & "</i></font><br>"
rs.movenext
loop
rs.Close
set rs = nothing

function getUSACount
	set rs = server.CreateObject("adodb.recordset")
	strSql = "SELECT COUNT(*) as USA" & strMemberTablePrefix & "MEMBERS WHERE M_COUNTRY = 'USA'"
	rs.Open strSql, my_Conn
	getUSACount = rs("USA")
	rs.Close
end function

function getCountryCount(psCountryName)
	Dim rs, strSql, lTemp

	set rs = server.CreateObject("adodb.recordset")
		strSql = "SELECT COUNT(MEMBER_ID) as CountryCount FROM " & strMemberTablePrefix & "MEMBERS WHERE M_COUNTRY = '" & chkString(psCountryName,"SQLString") & "'"
		rs.Open strSql, my_Conn
		lTemp = rs("CountryCount")
	rs.Close
	set rs = nothing

	getCountryCount = lTemp
end function

function getGenderCount(piGender) '# e.g. 1: male, 2 female ...
	Dim rs, strSql, lTemp

	set rs = server.CreateObject("adodb.recordset")
		strSql = "SELECT COUNT(MEMBER_ID) as GenderCount FROM " & strMemberTablePrefix & "MEMBERS WHERE M_SEX = " & CLng(piGender)
		rs.Open strSql, my_Conn
		lTemp = rs("GenderCount")
	rs.Close
	set rs = nothing

	getGenderCount = lTemp
end function

quote:

---

Originally posted by bjlt

how about this

function getCountryCount(psCountryName)
	Dim rs, strSql, lTemp

	set rs = server.CreateObject("adodb.recordset")
		strSql = "SELECT COUNT(MEMBER_ID) as CountryCount FROM " & strMemberTablePrefix & "MEMBERS WHERE M_COUNTRY = '" & chkString(psCountryName,"SQLString") & "'"
		rs.Open strSql, my_Conn
		lTemp = rs("CountryCount")
	rs.Close
	set rs = nothing

	getCountryCount = lTemp
end function

---

function getCountryCount(USA)
	Dim rs, strSql, lTemp

	set rs = server.CreateObject("adodb.recordset")
		strSql = "SELECT COUNT(MEMBER_ID) as USA FROM " & strMemberTablePrefix & "MEMBERS WHERE M_COUNTRY = '" & chkString(USAe,"SQLString") & "'"
		rs.Open strSql, my_Conn
		lTemp = rs("USA")
	rs.Close
	set rs = nothing

	getCountryCount = lTemp
end function

function getCountryCount(psCountryName)
	Dim rs, strSql, lTemp

	set rs = server.CreateObject("adodb.recordset")
		strSql = "SELECT COUNT(MEMBER_ID) as CountryCount FROM " & strMemberTablePrefix & "MEMBERS WHERE M_COUNTRY = '" & chkString(psCountryName,"SQLString") & "'"
		rs.Open strSql, my_Conn
		lTemp = rs("CountryCount")
	rs.Close
	set rs = nothing

	getCountryCount = lTemp
end function

your header and includes here
<%

'####### Global Variables #######
Dim sCountry, iMethod
Dim iPagesize
Dim iWhichPage, iTotalPages
Dim sSortMethod

sCountry = Trim(Request("country"))
iMethod = Clng(Request("method"))
if iMethod < 0 or iMethod > 3 then iMethod = 0

iPagesize = clng(strPageSize)


'######## Start Content #########

if sCountry = "" then Response.Redirect "MemberCountByCountry.asp" : Response.End '# MemberCountByCountry.asp is a file I use to list num of users for every country. change it to the page you like.

DoListUsersByCountry

'######### End Content ##########
WriteFooter


'####### Procedures Below #######
Sub DoListUsersByCountry
	Dim sSql, rs
	Dim aListByCountry, iRecCount
	Dim i, j
	Dim mMEMBER_ID, mM_NAME, mM_LEVEL, mM_EMAIL, mM_HOMEPAGE, mM_COUNTRY, mM_RECEIVE_EMAIL
	Dim MEMBER_ID, M_NAME, M_LEVEL, M_EMAIL, M_HOMEPAGE, M_COUNTRY, M_RECEIVE_EMAIL
	Dim sClass

	iWhichPage = GetPageNum(Trim(Request("whichpage")))

	'## get user list by country/nationality
	sSQL = "SELECT M.MEMBER_ID, M.M_NAME, M.M_LEVEL, M.M_EMAIL, M.M_HOMEPAGE, M.M_COUNTRY, M.M_RECEIVE_EMAIL FROM " & strMemberTablePrefix & "MEMBERS M WHERE M.M_STATUS = 1 AND M.M_COUNTRY = '" & chkString(sCountry,"SQLString") & "'"

	Select Case iMethod '# I use 0/1/2/3 for sex if you use male/female/etc change it accrodingly below
		Case 1
			sSQL = sSQL & " AND M.M_SEX = 1" '# = 'male'
		Case 2
			sSQL = sSQL & " AND M.M_SEX = 2" '# = 'female'
		Case 3
			sSQL = sSQL & " AND M.M_SEX <> 1 AND M.M_SEX <> 2" '# M.M_SEX <> 'male' AND M.M_SEX <> 'female'
		Case Else
			'
	End Select


	Set rs = Server.CreateObject("ADODB.Recordset")
		rs.cachesize = iPagesize

		rs.open sSQL, my_Conn, adOpenStatic, adLockReadOnly, adCmdText

		if rs.EOF or rs.BOF then  '## No Users found
			iRecCount = 0
		Else
			rs.pagesize = iPagesize
			iTotalPages = cLng(rs.pagecount)
			if iWhichPage > iTotalPages then iWhichPage = iTotalPages
			rs.absolutepage = iWhichPage
			aListByCountry = rs.GetRows(iPagesize)
			iRecCount = UBound(aListByCountry,2) + 1
		End If

	rs.close
	set rs = nothing

	Response.Write	"" &_
		"<table border=0 width=""100%"" cellpadding=""0"" cellspacing=""0"" align=""center"">" & vbNewLine & _
		"	<tr>" & vbNewLine 
	Call DropDownPaging(1)
	Response.Write	"" &_
		"	</tr>" & vbNewLine & _
		"</table>" & vbNewLine &_
		"<table align=""center"" class=""TblList"">" & vbNewLine & _
		"	<tr>" & vbNewLine &_
		"		<th class=""TblHeadCellDefault"">Contact</th>" & vbNewLine & _
		"		<th class=""TblHeadCellDefault"">Member Name</th>" & vbNewLine &_
		"	</tr>" & vbNewLine

	If iRecCount = 0 Then
		Call WriteNoRecCells(2)
	Else
		mMEMBER_ID = 0
		mM_NAME = 1
		mM_LEVEL = 2
		mM_EMAIL = 3
		mM_HOMEPAGE = 4
		mM_COUNTRY = 5
		mM_RECEIVE_EMAIL = 6
		'mM_PMRECEIVE = 7

		i = 0
		j = 0

		For i = 0 to iRecCount - 1
			MEMBER_ID = aListByCountry(mMEMBER_ID,i)
			M_NAME = aListByCountry(mM_NAME,i)
			M_LEVEL = aListByCountry(mM_LEVEL,i)
			M_EMAIL = aListByCountry(mM_EMAIL,i)
			M_HOMEPAGE = aListByCountry(mM_HOMEPAGE,i)
			M_COUNTRY = aListByCountry(mM_COUNTRY,i)
			M_RECEIVE_EMAIL = aListByCountry(mM_RECEIVE_EMAIL,i)
			'M_PMRECEIVE = aListByCountry(mM_PMRECEIVE,i)

			if j = 0 then
				sClass = "TblCellDefault"
			else
				sClass = "TblAltCellDefault"
			end if

			Response.Write	"" &_
			"	<tr>" & vbNewLine & _
			"		<td align=""left"" class=""" & sClass & """>put email, homepage etc codes here</td>" & vbNewLine &_
			"		<td align=""center"" class=""" & sClass & """><a href=""pop_profile.asp?mode=display&id=" & MEMBER_ID & """ target=""profile"">" & ChkString(M_NAME,"display") & "</a></td>" &_
			"	</tr>" & vbNewLine

			j = j + 1
			if j = 2 then j = 0
		Next
	End if

	Response.Write	"" &_
		"</table>" & vbNewLine &_
		"<table border=0 width=""100%"" cellpadding=""0"" cellspacing=""0"">" & vbNewLine & _
		"	<tr>" & vbNewLine
	Call DropDownPaging(2)
	Response.Write	"" &_
		"	</tr>" & vbNewLine & _
		"</table>" & vbNewLine

End Sub


sub DropDownPaging(piNum)
	Dim sAlign, i
	if piNum mod 2 = 0 then
		sAlign = "left"
	Else
		sAlign = "right"
	End If

	if iTotalPages > 1 then
		Response.Write "" &_
		"<form name=""PageNum" & piNum & """ action=""ListUsersByCountry.asp"">" & vbNewLine &_
		"	<input name=""method"" type=""hidden"" value=""" & iMethod & """>" & vbNewLine &_
		"	<input name=""country"" type=""hidden"" value=""" & sCountry & """>" & vbNewLine &_
		"		<td align=""" & sAlign & """>" & vbNewLine
		if iWhichPage > 1 then 
				Response.Write "" &_
		"		<a href=""ListUsersByCountry.asp?country=" & ChkString(sCountry,"urlpath") & "&method=" & iMethod & "&whichpage=" & iWhichPage - 1 & """>Previous Page</a>" & vbNewLine
		end if
		
		Response.Write "<b>Page</b>" & vbNewLine &_
		"		<select name=""whichpage"" size=""1"" onchange=""ChangePage(" & piNum & ");"">" & vbNewLine
		for i = 1 to iTotalPages
			Response.Write "" &_
		"			<option value=""" & i & """"
		if i = iWhichPage then response.write "selected"
		response.write "" &_
		">" & i & "</option>" & vbNewLine
		next
		Response.Write "" &_
		"		</select>" & vbNewLine &_
		"		<b>Total: " & iTotalPages & "</b>? & vbNewLine
		if iWhichPage < iTotalPages then 			
			Response.Write "" &_
		"		<a href=""ListUsersByCountry.asp?country=" & ChkString(sCountry,"urlpath") & "&method=" & iMethod & "&whichpage=" & iWhichPage + 1 & """>Next Page</a>" & vbNewLine
		end if
		Response.Write "" &_
		"		</td>" & vbNewLine &_
		"</form>" & vbNewLine
	else
		Response.Write "<td>?lt;/td>"
	end if
end sub 

Sub WriteNoRecCells(colspan)
	Response.Write	"" &_
		"	<tr>" & vbNewLine & _
		"		<td class=""TblCellDefault"" colspan=""" & colspan & """ align=""center""> No Members </td>" & vbNewLine &_
		"	</tr>" & vbNewLine
End Sub

Function GetPageNum(piPage)
	if piPage = "" or not isnumeric(piPage) then
		GetPageNum = 1
	else
		GetPageNum = Clng(piPage)
		if GetPageNum < 1 then GetPageNum = 1
	end if
End Function
%>