Best Event Rating Query Calculation IssueRSS

3 replies

Last post Dec 13, 2017 08:40 AM by Deepak Panchal

• Rameezwaheed

Contributor

2599 Points

1754 Posts

Best Event Rating Query Calculation Issue

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

life is name of learning!
Mark as an answer if it helps
• mgebhard

All-Star

37001 Points

14926 Posts

Re: Best Event Rating Query Calculation Issue

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).

• Rameezwaheed

Contributor

2599 Points

1754 Posts

Re: Best Event Rating Query Calculation Issue

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,

life is name of learning!
Mark as an answer if it helps
• Deepak Panch...

Contributor

2930 Points

1210 Posts

Re: Best Event Rating Query Calculation Issue

Dec 13, 2017 08:40 AM|Deepak Panchal|LINK

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

MSDN Community Support