Last post Dec 13, 2017 08:40 AM by Deepak Panchal
Dec 10, 2017 11:19 AM|Rameezwaheed|LINK
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
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?
Dec 10, 2017 01:49 PM|mgebhard|LINK
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).
Dec 12, 2017 04:46 AM|Rameezwaheed|LINK
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.
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
Select Min(Name) as Name,Email,Sum(Points) as Points
,Count(*) as Games_Played,AVG(Points) as Average_Points
From @a Group by Email
order by Rank
sql Server: Rank by sum of points and order by ranking