creating a best sellers list from my Orders Table 2

[aprompt1971]

I'm creating an online shop which has an area showing the top ten best selling products. I'd like to generate the list of the best selling products from my OrdersDetail table (which is table showing listing purchases made on the site), ordered by the most popular at the top and the least popular at the bottom. I thought if I could use the SQL DISTINCT command this would create a list of products without duplicates, then if I could COUNT them I'd generate a list of the most popular products in the OrdersDetails table. This nearly works but the list won't ORDER BY the COUNT column (I get an error). This is my code so far:

SELECT DISTINCT ProductDescription, COUNT(ProductDescription) AS bestSellers
FROM OrdersDetail
GROUP BY ProductDescription

Anyone got any ideas, I know this is more of a SQL query but I'm stuck.

 

[r937]

DISTINCT is almost always redundant when using GROUP BY

try this --

select productdescription
     , count(*) as bestsellers
  from ordersdetail
group 
    by productdescription
order
    by count(*) desc

r937.com | rudy.ca

 

[p5wizard]

SELECT ProductDescription, COUNT(ProductDescription) AS bestSellers
FROM OrdersDetail
GROUP BY ProductDescription
ORDER By 2 desc

the GROUP BY already eliminates duplicates IMHO
the ORDER BY takes care of sorting the result table

But wouldn't you also want to take into account the quantity ordered?

SELECT ProductDescription, SUM(QuantityOrdered) AS bestSellers
FROM OrdersDetail
GROUP BY ProductDescription
ORDER By 2 desc



HTH,

p5wizard

 

[aprompt1971]

thanks, works a treat

 

[aprompt1971]

Good point p5wizard, I hadn't thought of that and it does make more sense as the quantity of each product bought is more important than just whether it was bought.

Now I've got my working best sellers list I want to make it a link to the product detail page and therefore I need to add the Product ID number "IdProductDetail" into the SQL so that I can reference it in the ASP code. I've never really got to grips with GROUP BY and when I add in the Product ID into the SQL I get an error, this is the amended SQL:

SELECT IdProductDetail, ProductDescription, SUM(Quantity) AS bestSellers
FROM OrdersDetail
GROUP BY ProductDescription
ORDER By 2 desc

Thanks again.

 

[r937]

whenever you use GROUP BY, you must include in the GROUP BY every non-aggregate column from the SELECT

also, ORDER BY 2 refers to the 2nd column in the result set, i think you now want to order by the 3rd -- which is why it's often better not use ordinal numbers in the ORDER BY

select [b]idproductdetail[/b]
     , [b]productdescription[/b]
     , sum(quantity) as bestsellers
  from ordersdetail
group 
    by [b]idproductdetail[/b]
     , [b]productdescription[/b]
order 
    by sum(quantity) desc

r937.com | rudy.ca

 

[aprompt1971]

thanks very much for all your help, all working fine now.