Top 10 items?

I'm trying to write a little ASP routine to display the top 10 items sold.
Following is a sample database:

Sale #: Name: Quantity:
1 Vodka 2
2 Beer 3
3 Beer 1
4 Vodka 1

Rough sketch of what I want to do:

Do While Not MyRS.EOF
total(NAME) = total(Name) + MyRS("Quantity")
MyRS.MoveNext
Loop

The array values would look like this:
total(Vodka) = 3
total(Beer) = 4

Then if I sort it by most amount sold, I'd get
Beer = 4
Vodka = 3

My production database has over 10,000 sales in it and I'm trying to get the
top 10.

Re: Top 10 items?

TRB_NV wrote:
> I'm trying to write a little ASP routine to display the top 10 items
> sold. Following is a sample database:
>
> Sale #: Name: Quantity:
> 1 Vodka 2
> 2 Beer 3
> 3 Beer 1
> 4 Vodka 1
>
> Rough sketch of what I want to do:
>
> Do While Not MyRS.EOF
> total(NAME) = total(Name) + MyRS("Quantity")
> MyRS.MoveNext
> Loop
>
> The array values would look like this:
> total(Vodka) = 3
> total(Beer) = 4
>
> Then if I sort it by most amount sold, I'd get
> Beer = 4
> Vodka = 3
>
> My production database has over 10,000 sales in it and I'm trying to
> get the top 10.

Never ask a database-related question without telling us the type and
version of database you are using.

If your database supports standard sql, you can do this with a query:

select name, sum(quantity) as TotalSold
from yourtable
group by name
order by sum(quantity) desc

And if your database supports the TOP keyword, you can do this:

select top 10 name, ...


HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: Top 10 items?

Perfect!! Worked the first time.

The database I'm querying is in Access 2000, which my client uploads via FTP
to the webserver from his point of sale system.

Final code looks like this:

<%@ LANGUAGE="VBSCRIPT" %>

<%
Dim MyConn, MyRS, MySQL

response.write "