Best Event Rating Query Calculation Issue

• 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

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.

Thanking you,

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

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