Classic ASP - Trying to retrieve total count of specific values

Question

I really hope someone can help me here. I am currently coding in classic asp, and I work with an SQL Database.

We have a support page that logs all visits using their network login id into an sql table. And this is where I am having an issue.

I need to display the top 10 highest visitors of the page and the top 10 lowest used visitors. So for instance, if you see below, this is similar to how I want to see the data on the page:

---

Visitor Visits

• John 50
• Sarah 45
• Tim 40
• Jason 35
• Alex 30
• Rob 25
• Elain 20

---

I cannot for the life of me, figure out how to get this info. All I want is to get a total count of all the visitors visits. Now, we have the data in the database. I just do not know how to place the total count for each visitor on a page and then format the layout from most visits to lowest visits. I have tried everything from searching on the net, and even here. Nothing quite gives me what I need to get this working. I have even tried different methods of using count and total, but, the only successful query is with total visits overall, and I just need it broken down by the visitor column. Can someone please assist me?

UPDATE:

Based on the responses, I am not sure what code to provide. I do not have any code to give. I am simply trying to code in asp-classic a way to query a total count from a specific column for each value. So for instance, if the name "James" visited the site 10 times and the database has him saved 10 times, I would like to query that total on asp. I do not know how to grab the count from the database for each name in the column and get their total visits.

Update 2:

Ok, so this is the code I am trying to fiddle with, based on other resources suggestions on the net:

Set Logs = Connect.Execute ("SELECT COUNT(*) AS VISITOR FROM dbo.Tracking GROUP BY VISITOR

Answer 1

I just wanted to say thank you for all of everyones help. A few days after OP was answered, I was able to come up with a way to make your suggestions work for my business needs. I wanted to share an example of what I got working based on your assistance.

I used a specific date format in SQL and had to change the date to an array to make this work. I was even able to add rank! I know it looks amateurish, but it works. And now I understand how it works. Thanks again for all your help.

Below is the example in case anyone is interested (I know its a little late, but figured I would share):

Set Logs = Connect.Execute ("SELECT TOP 20 AnalystID, Count(*) AS N FROM dbo.Analysts WHERE Visits = '" & DateArr(0) & DateArr(2) & "' AND AnalystID <> 'siteadmin1' AND AnalystID <> 'siteadmin2' GROUP BY AnalystID ORDER BY COUNT(*) DESC")


If Logs.eof Then

 Else
Do While Not Logs.Eof
iRank = iRank + 1


    Set Con = Server.CreateObject("ADODB.Connection") 
    Con.Open = "Provider=SQLOLEDB;Data Source=" & DatabaseServer & ";Integrated Security=SSPI;Network Library=DBMSSOCN;Initial Catalog=HRDB;"
            Set myRecordSetRDR = Con.Execute ("SELECT * FROM dbo.HRDBTBLL With(NoLock) WHERE WDW_LGON_ID = '" & Logs("AnalystID") & "'")
            If myRecordSetRDR.EOF Then
            Else
                strANALYSTFULLNAMEReport = myRecordSetRDR("WKR_FLL_NM")
                'response.write myRecordSetRDR("WKR_FLL_NM")
            End If 
    Con.Close () 

    If left(Logs("AnalystID"),3) = "NBE" Then
        strANALYSTFULLNAMEReport = "N/A"
    End If

    If left(Logs("AnalystID"),2) = "ZE" Then
        strANALYSTFULLNAMEReport = "N/A"
    End If      
response.write "<tr>"
    response.write "<td>"
    Response.Write "<center>" & iRank & "</center>"
    Response.write "</td>"
    response.write "<td>" & formatnumber(Logs("N"),0) & "</td><td>" & Logs("AnalystID") & "</td><td>" & strANALYSTFULLNAMEReport & "</td>"
    response.write "</tr>" 
Logs.Movenext
Loop

Answer 2

As I'm getting criticised for not helping...

@kwnwzlnd has provided you the database query part of the puzzle but from what you have said in the question you have no idea how to put that into an ASP page well here is an example.

Bit of Background First

Classic ASP out of the box uses the scripting language VBScript which relies heavily on COM components to provide extended functionality like talking to a database. In the VBScript world the library that facilitates this is called ADO (ActiveX Data Objects) and is part of MDAC (Microsoft Data Access Components).

ADODB provides access to objects that allow you to perform complex operations against a database, these objects include (among others);

• ADODB.Connection - Represents an open connection to a data source.
• ADODB.Recordset - Represents the entire set of records from a base table or the results of an executed command. At any time, the Recordset object refers to only a single record within the set as the current record.
• ADODB.Command - Defines a specific command that you intend to execute against a data source.

A Basic Example

<%
Option Explicit

Dim cmd, sql, conn_str, rs
'Connection String used to connection to the MySQL 
'database - see https://www.connectionstrings.com/mysql/
conn_str = "Driver={MySQL ODBC 5.1 Driver};Server=myServerAddress;Database=myDataBase;User=myUsername;Password=myPassword;Option=3;"
sql = ""
sql = sql & "SELECT Table1.Visitor, Count(Table1.Visitor) AS Visits" & vbCrLf
sql = sql & "FROM Table1" & vbCrLf
sql = sql & "GROUP BY Table1.Visitor" & vbCrLf
sql = sql & "ORDER BY Count(Table1.Visitor) DESC;" & vbCrLf
sql = sql & "LIMIT 10;"

Set cmd = Server.CreateObject("ADODB.Command")
With cmd
  .ActiveConnection = conn_str
  .CommandType = adCmdText 'Named Constant value equals 1
  .CommandText = sql
  'Execute query and return results to a ADODB.Recordset
  Set rs = .Execute()
End With
%>
<ul>
<%
Do While Not rs.EOF
%>
  <li><%= rs("Visitor") & " - " & rs("Visits") %></li>
<%
  'Move to the next record in the returned Recordset.
  Call rs.MoveNext()
Loop
%>
</ul>
<%
'Cleanup after ourselves
Call rs.Close()
Set rs = Nothing
Set cmd = Nothing
%>

^{Code is provided untested}

If I was doing this there are things I would do differently this is just meant as a starting point and moving forward would definitely recommend using GetRows() method of the ADODB.Recordset to return an 2D Array of the recordset that way you can close the database connection and carry on using the data without the database overhead.

Bear in Mind

Even with this simple example it will likely raise more questions then it answers.

Answer 3

Sorry, MS-SQL not MySQL

I think you have to group the Names and Count them after that. Select the Top 10 ordered by Count DESC and ASC.

SELECT TOP 10 Table1.Visitor, Count(Table1.Visitor) AS Visits
FROM Table1
GROUP BY Table1.Visitor
ORDER BY Count(Table1.Visitor) DESC;

UPDATE 1

MySQL uses LIMIT instead of TOP (@w3schools):

SELECT Table1.Visitor, Count(Table1.Visitor) AS Visits
FROM Table1
GROUP BY Table1.Visitor
ORDER BY Count(Table1.Visitor) DESC;
LIMIT 10;