MySQL Question
Classic ASP - Trying to retrieve total count of specific values
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
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;
Source (Stackoverflow)