Counting repetitions in a database

Results 1 to 3 of 3

Thread: Counting repetitions in a database

  1. #1
    Join Date
    Dec 1969

    Default Counting repetitions in a database

    I am in desperate need of some guidance! Please help ;-)<BR><BR>Is it possible to get statistics using ASP and SQL?<BR>- Say, for example, I have a table of sales data, and I want to see how many times “john” sold something, and create a list of top 10 salesmen.<BR><BR>I had seen it done before, especially in log file analysers – for example, top 10 accessed pages, and so on. I just have no idea were to start, let alone how to do it.<BR>(I have used ASP and SQL all the time, so I do know a little bit, but this particular task is beyond my abilities)<BR><BR>Any help is greatly appreciated!

  2. #2
    Join Date
    Dec 1969

    Default I think you want an aggregate query

    Not sure what your table structure is like but if for example you had table Sales with a column Name<BR><BR>Select Count(Name) as JohnCount from Sales where Name=&#039;john&#039;<BR><BR>This would get all the entries in the table for john and count them. If you wanted a count of everyone you&#039;d do something like:<BR><BR>select count(Name), Name from Sales Group By Name<BR><BR>This would give a count of each name with the name next to the count. Normally your names would be in a different table and only the ID of th name would be in the sales table but that&#039;s an example anyway. w3schools has a decent tutorial on this:<BR><BR>

  3. #3
    Join Date
    Dec 1969

    Default And if you want top 10 salesmen...

    SELECT TOP 10 Name, Count(Name) AS salesCount<BR>FROM Sales<BR>GROUP BY Name<BR>ORDER BY Count(Name) DESC<BR><BR>If you have been working with ASP and SQL for a while and have never run into something like this, then yes, it is more than time go learn a *lot* more about SQL.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts