Top sellers SQL ?

[btn]

Hi - using asp/access/vbscritp

Have a webshop where all orders are stored in a cart table in the database.
Need an advice on how to get an SQL that filter the 5 most sold products (The
ProductID that has been repeated most times 1-5) in the cart table, and then
make an output list of product 1 to 5 like this:

Most sold:
1. Productname01
2. Productname26
3. Productname55
4. Productname65
5. Productname100

Any suggestions?

Bjorn.

[JScavitto]

with something like this:

SELECT TOP 5
OrderDetails.ProductID,
SUM(OrderDetails.Quantity) as TotalNum,
Products.ProductName

FROM
OrderDetails
INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID

GROUP BY
OrderDetails.ProductID,
Products.ProductName

ORDER BY
TotalNum DESC





--

JScavitto
Home of the UltraCart Shopping Carts
[url]www.ottivacsdesign.com[/url]
[url]www.thechocolatestore.com[/url]


"btn" <webforumsuser@macromedia.com> wrote in message
news:d74vta$92m$1@forums.macromedia.com...

> Hi - using asp/access/vbscritp
>
> Have a webshop where all orders are stored in a cart table in the
> database.
> Need an advice on how to get an SQL that filter the 5 most sold products
> (The
> ProductID that has been repeated most times 1-5) in the cart table, and
> then
> make an output list of product 1 to 5 like this:
>
> Most sold:
> 1. Productname01
> 2. Productname26
> 3. Productname55
> 4. Productname65
> 5. Productname100
>
> Any suggestions?
>
> Bjorn.
>

[Paul Whitham TMM]

Write a query in the database to count the various times that the
ProductName appears and set it to output in desc order. The use this query
as you source for your SQL statement on the webpage but add the "Top 5" to
limit the display like this

Select Top 5 ProductName ProductCount
From qryTopProd
Order by ProductCount Desc

--
Regards

Paul Whitham
Macromedia Certified Professional for Dreamweaver MX2004
Valleybiz Internet Design
[url]www.valleybiz.net[/url]

Team Macromedia Volunteer for Ultradev/Dreamweaver MX
[url]www.macromedia.com/support/forums/team_macromedia[/url]

"btn" <webforumsuser@macromedia.com> wrote in message
news:d74vta$92m$1@forums.macromedia.com...

> Hi - using asp/access/vbscritp
>
> Have a webshop where all orders are stored in a cart table in the

database.

> Need an advice on how to get an SQL that filter the 5 most sold products

(The

> ProductID that has been repeated most times 1-5) in the cart table, and

then

> make an output list of product 1 to 5 like this:
>
> Most sold:
> 1. Productname01
> 2. Productname26
> 3. Productname55
> 4. Productname65
> 5. Productname100
>
> Any suggestions?
>
> Bjorn.
>