locked
Best Event Rating Query Calculation Issue RRS feed

  • Question

  • User1623409651 posted

    Hi all,

    I have below two table  for storing Events and their Ratings, Where Every Registered Student Can Rate The event, Now i have to found the best event from their ratings

    Table 1. StudentEvents EventID EventName
    Table 2 EventRating RatingID EventID Rating StudentID

    I have below Query

     SELECT SERating.EventID As EventID,  ROUND(ISNULL(CAST(SUM(SERating.Rating) AS NUMERIC(5, 2)),0),1) As TotalRatingSUM, COUNT(DISTINCT SERating.StudentID) As TotalParticipate, 
      ISNULL((SELECT AVG(Rating) FROM Events WHERE  StudentEvents.EventId = SERating.EventId), 0 ) As AverageEventRating 
      FROM StudentEvents  As SERating 
      GROUP BY SERating.EventID

    Is there any Idea Where i am doing wrong in calculation?

    Thanks

    Sunday, December 10, 2017 11:19 AM

All replies

  • User475983607 posted

    This query will give you the Average rating by Event assuming the rating contains numeric values.

    SELECT se.EventID, AVG(ev.Rating)
    FROM StudentEvent AS se
    	INNER JOIN EventRating AS er ON se.EventID = er.EventID
    GROUP BY se.EventID

    For here you can use the query above as a subquery or order the results by AVG(ev.Rating).

    Sunday, December 10, 2017 1:49 PM
  • User1623409651 posted

    Thanks mgebhard for your reply,

    Yes Rating Contains numeric values i.e Excellent = 5, V.Good = 4 , Good = 3 , Average = 2 and  Poor = 1

    It means that based on the average value we will find the best one event . suppose the average value of one event is 4 it means the event Rating is Very Good ? if the average value = 1 means Event Rating is poor ? am i right.

    Please clearify.

    Thanking you,

    Tuesday, December 12, 2017 4:46 AM
  • User347430248 posted

    Hi Rameezwaheed,

    you can also try to refer query below will give you an idea.

    Select Rank() over (order by Points desc) as Rank
    ,Name,Points,Games_Played,Average_Points
    from
    (
    Select Min(Name) as Name,Email,Sum(Points) as Points
    ,Count(*) as Games_Played,AVG(Points) as Average_Points
    From @a Group by Email
    ) a 
    order by Rank

    Reference:

    sql Server: Rank by sum of points and order by ranking

    Regards

    Deepak

    Wednesday, December 13, 2017 8:40 AM