SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    I'm trying to translate this functional Access query into mySQL. Help!

    I have 2 tables:

    books (isbn, title, author, category1, category2, category3)

    ratings (id, isbn, rating, comment, date entered, submitted by)

    They are realted by isbn (primary key in books table). I need the quwry to tell me what the top 10 rated books are and the top 10 rated books by category.
    At the moment I'm trying to work out the category query. In Access this works (it was autogenerated when I used a wizard:

    SELECT ratings.isbn AS ISBN, Avg(ratings.rating) AS Average_Rating, books.title AS Title, books.author AS Author
    FROM books, ratings
    WHERE (((books.isbn)=[ratings].[isbn]))
    GROUP BY ratings.isbn, books.title, books.author
    ORDER BY Avg(ratings.rating) DESC;

    I've translated it, for mySQL/PHP as this:

    SELECT ratings.isbn AS ISBN, Avg(ratings.rating) AS Average_Rating,
    books.title AS Title, books.authors AS Authors
    FROM books, ratings
    WHERE books.isbn=ratings.isbn
    AND books.category1 = '$ratings_category'
    GROUP BY ratings.isbn, books.title, books.author
    ORDER BY Avg(ratings.rating) DESC

    2 problems:
    1. I get an error saying "Invalid use of group function"
    2. I want to be able to say books.category1 = '$ratings_category'
    OR books.category2 = '$ratings_category' OR books.category3 = '$ratings_category'

    What is going wrong with Q1 and can you use OR like I say in Q2?

  2. #2

    Join Date
    Oct 2003
    Location
    €uroLand
    Posts
    1,340
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Try this
    Code:
    SELECT ratings.isbn AS ISBN, Avg(ratings.rating) AS Average_Rating, books.title AS Title, books.authors AS Authors
    FROM books, ratings
    WHERE books.isbn = ratings.isbn AND (books.category1 ='$ratings_category'
     OR books.category2 ='$ratings_category'
     OR books.category3 ='$ratings_category')
    GROUP BY ratings.isbn, books.title, books.authors
    ORDER BY Average_Rating DESC;

  3. #3
    SitePoint Addict
    Join Date
    Mar 2004
    Location
    Europe
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Amazing! Works first time. Thanks very much.

    How is it so different? The group by clause is the same. ?? Confused ??


Bookmarks

Posting Permissions

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