MySQL Crew... getting Truncation error. Plz help

Discussion in 'OT Technology' started by brds, Apr 7, 2008.

  1. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    Trying to run the query:
    Code:
    SELECT ratings.rating, COUNT(ratings.rating) AS 'num_ratings'
    FROM ratings
    WHERE 'num_ratings' > 10000
    GROUP BY ratings.rating;
    
    and i'm getting an error of

    ratings.rating holds integers. How can i make the count work right?
     
  2. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    I'm also getting the same error with the following query:

    Code:
    SELECT movies.title, COUNT(ratings.rating) AS 'ratings'
    FROM movies INNER JOIN ratings
    ON movies.movieid=ratings.movieid
    WHERE 'ratings' > 25
    GROUP BY movies.title;
    
     
  3. JayDee07

    JayDee07 New Member

    Joined:
    Aug 27, 2004
    Messages:
    794
    Likes Received:
    0
    Just taking a wild stab:

    Maybe change the where clause to WHERE 'ratings' > 25.00?
     
  4. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    Tried that... didn't work :wtc:

    I think that 'ratings' is a double and what it's being compared to is an int.

    Is there a way to do something like

    Code:
    CONVERTtoINT(COUNT(ratings.rating))
    or something like that?
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    table def?
     
  6. Limp_Brisket

    Limp_Brisket New Member

    Joined:
    Jan 2, 2006
    Messages:
    48,422
    Likes Received:
    0
    Location:
    Utah
    num_ratings shouldn't be in quotes

    you can use CAST() to cast.
     
  7. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    didn't work with removing the quotes. Also tried to do cast... i tried:

    Code:
    SELECT ratings.rating, COUNT(ratings.rating) AS 'num_ratings'
    FROM ratings
    WHERE CAST('num_ratings' AS INT) > 10000
    GROUP BY ratings.rating;
    
    and i still got the same error
     
  8. lukin87

    lukin87 New Member

    Joined:
    Jan 11, 2008
    Messages:
    135
    Likes Received:
    0
    you're doing it wrong.

    when you want to run a test on an output of a grouped query you need to use the "having" statement.

    Also in mysql you don't actually need the "AS" when you want to rename a column you can just put a space and type the column heading.

    Also num_ratings should not be in quotes.

    Here this should do what you're after:

    Code:
     
    SELECT ratings.rating, COUNT(ratings.rating) num_ratings
    FROM ratings
    GROUP BY ratings.rating
    HAVING num_ratings > 10000;
    
     
  9. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    Thank you, you're awesome!
     
  10. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    One more question i'm having a problem with...

    the question is "How many movies have been rated?"

    By this, i'm assuming my teacher means that how many unique movies have been rated, and not how many ratings are there. The table def. is as follows:

    movies:
    PK: movieid
    release year
    title

    ratings:
    PK: ratingid
    customerid
    rating
    rdate
    FK: movieid

    I'm not sure how to make each movie only count once

    Any thoughts?
     
  11. critter783

    critter783 OT Supporter

    Joined:
    Jul 15, 2005
    Messages:
    1,785
    Likes Received:
    0
    SELECT COUNT(DISTINCT ratings.movieid)
    FROM ratings;
     
  12. brds

    brds OT Supporter

    Joined:
    Jun 26, 2006
    Messages:
    17,642
    Likes Received:
    13
    Location:
    Atlanta
    :love:
     

Share This Page