SQL Server Performance

Comparing count between fields - need SQL query help

Discussion in 'Getting Started' started by linney21, Jan 5, 2011.

  1. linney21 New Member

    Hi, thank you for your help. I have the following tables. My goal is to populate Table A with the overall rating count of Table C for each asset in Table A.
    A) asset table
    asset_id asset_name overall_rating
    1 video1 <null>
    2 video2 <null>
    B) rating table
    rating_id rating
    1 highest
    2 high
    3 neutral
    4 low
    5 lowest
    C) link_asset_to_rating (Based on how people rate the videos, the values go into this table)
    asset_to_ratingID asset_id rating_id
    1 1 2
    2 1 3
    3 1 2
    4 1 5
    5 1 2
    6 2 1
    7 2 2
    8 2 3
    For the scenrio in Table C, I have a query that will give me the count and the max count of the values in that table (see below). So for Asset #1, the overall rating field in Table A would be 2 since '2' appeared three times in Table C and has the most ratings for that rating_id. BUT for Asset #2, there is a 3 way tie for the rating_id. How do I create a query that will compare each count of rating_id and retrieve the highest rating for that asset? So for Asset#2, I want the value '1' to be the overall rating since "highest" is the most positive response even though there was a 3 way tie.
    Please let me know if I need to clarify. Here is my count query that I made into a view and my max query of the count:
    CREATE VIEW vwCountOfRatingID
    AS
    SELECT asset_id, Count(rating_id) as CountOfRatingID, rating_id
    FROM link_asset_to_rating
    GROUP BY asset_id, rating_id

    SELECT Max(CountOfRatingID)
    FROM vwCountOfRatingID
    WHERE vwCountOfRatingID.asset_id=2;




  2. preethi Member

    I have a question:
    How will you determine what value '1' to be the overall rating for asset #2. from your data I can see that "Highest" is the highest rating and "neutral" is higher than "low" but how SQL Server can determine that? Can we safely assume that "lower the Rating_id is higher the rating"?
    If so, I believe this query will help you
    ;with CTE (asset_id , CountOfRatingID , Rating_id )
    AS
    (
    SELECT asset_id, Count(rating_id) as CountOfRatingID, rating_id
    FROM link_asset_to_rating
    GROUP BY asset_id, rating_id
    )
    Select A.asset_id, a.rating_id, a.CountOfRatingID
    from CTE a left join CTE b
    on a.asset_id= b.asset_id and a.CountOfRatingID<=b.CountOfRatingID and a.Rating_id>b.Rating_id
    where b.Rating_id is null
    Please note that the first character of the code starts with semicolon.
  3. linney21 New Member

    Sir - Your assumption is correct that the lower the rating_id the higher the rating. I tried your query and it worked exactly as needed. You are a genius! Thank you. I will try to incorporate it into my other query but if I have any issues, I may need to ask for more assistance but for now, you got me off to a good start. Thank you again!
  4. linney21 New Member

    Can you explain to me how the query above works? I understand parts of it but do not fully understand how it works. Also, what if I wanted 5 to be the highest rating? How does that impact the query using the CTE above? Thank you!

Share This Page