Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    2

    Angry Unanswered: Dual HAVING on AS

    Hi all, new here...

    I have this, working and is fast.

    PHP Code:
    SELECT movie.idmovie.titlemovie.cat_1,
    movie.prod_yearmovie.director_1movie.imagevote.movieid,
    ROUND(SUM(vote.vote)/COUNT(vote.vote),2) AS Average
    FROM movie
    vote WHERE vote.movieid=movie.id
    AND movie.image != 'missing.png'
    AND movie.prod_year >= '2000'
    AND movie.image IS NOT NULL GROUP BY vote.movieid
    HAVING Average 
    >= '8'
    ORDER BY RAND()
    LIMIT 5
    Now I want to add something like this. I want to show the movies with ten votes or more. Can't get it to work

    PHP Code:
    SELECT movie.idmovie.titlemovie.cat_1movie.prod_yearmovie.director_1movie.imagevote.movieid,
    ROUND(SUM(vote.vote)/COUNT(vote.vote),2) AS Average,
    COUNT(vote.vote),2) AS Votes
    FROM movie
    vote
    WHERE vote
    .movieid=movie.id
    AND movie.image != 'missing.png'
    AND movie.prod_year >= '2000'
    AND movie.image IS NOT NULL
    GROUP BY vote
    .movieid
    HAVING Average 
    >= '8'
    HAVING Votes >= '10'
    ORDER BY RAND()
    LIMIT 5
    Anyone have an idea?

    Best regards

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use ANDs and ORs to make compound HAVING conditions, the same way that you use ANDs and ORs to make compound WHERE conditions

    Code:
    HAVING Average >= 8 
       AND Votes >= 10
    note: use numeric constants when comparing with numeric values (i.e. don't put quotes around them)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2010
    Posts
    2
    Worx perfect! I did try AND, but like 'AND HAVING >= 10', using HAVING again

    Thanks alot

Posting Permissions

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