Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2006

    Unanswered: Efficient best sellers list

    I need to display a best selling product list but I'm clueless as to the best implementation. It would be either on a monthly or a weekly basis (monthly to start off with, then weekly after more sales occur). Would it be easy enough to pull the records with the most occurrences in the tables?

    As an example, something like SELECT * FROM items, sales ORDER BY OCCURRENCES(item_no) DESC

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    if you want a best sellers list, then the time period is failry irrelevant to the query design, althouygh it is important to the way you use the information.

    I'd suggest you have a look at the SQL aggregate functions such as count

    this may be of help

    you can then apply your date band using a where clause

    you may want tio consider a LIMIT, pulling off the top n or top n%.

    So I'd expect something like
    select ISBN, booktitile, author, count(ISBN) as NoSales, Sum(SalesValue) as SalesAmount from BookSales purchasedate>=xxxxxx and PurchaseDate<=yyyyyy group by ISBN order by NoSales Desc Limit zzz
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2006
    I've been hunting and experimenting and this seems to work perfectly for me:
    $query = "SELECT *, COUNT(cat_no) AS occurences FROM sales_digital GROUP BY cat_no, trk_no, disc_no ORDER BY occurences DESC LIMIT 10";


  4. #4
    Join Date
    Apr 2002
    Toronto, Canada
    if it seems to work, then it only seems to work

    you're going to get one row in the result set for every unique combination of cat_no, trk_no, disc_no

    any time you find yourself using the dreaded, evil "select star" and GROUP BY at the same time, chances are really good that you have an invalid GROUP BY statement, even though mysql will happily and merrily execute it for you anyway | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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