Get the Average Score and display

Question

Hi everyone, I have a question on the TSQL Syntax. Now, I have 2 table which is Service & Rating: Service: Rating: As you can see, the Rating_Id in the Rating table has many Services_Id values = 1 Now I want to get the Average Rating Score and display on the webpage, the TSQL that I tried: SELECT AVG(r.Score),s.* FROM Services s FULL OUTER JOIN Rating r ON r.Services_Id = s.Services_Id GROUP BY s.Services_id, r.Services_Id As I want to display, all the services that have and do not have ratings and for those that have ratings, I want to calculate Average Rating score. How can I achieve that Please help Thank you

Amit Gupta · Answer

This query will give you all the records from the serice table and average as null from rating table if service_id doesn't match. (like service_id 4) select s.*, ( select AVG (score) from #rating r where s.service_id = r.service_id and s.service_status = 'active' group by r.service_id ) as average from #service s Do let us know, if it solves your issue

Amit Gupta · Answer

Use left join (services table) and based on serviceid fetch the avg from ratings table.

Amit Gupta · Answer

Try like below ; with cte as ( select service_id, avg (score) as average from #rating group by service_id ) select s.*, c.average from #service s, cte c where s.service_id = c.service_id and s.service_status = 'active'

satheesh d · Answer

We can use the inbuilt function AVG() for calculating the average and finnaly it is grouped by service id. SELECT serv.Service_Name, serv.Service_Id, AVG(rate.rateScore) AS Average from TblService serv JOIN TblRating rate on serv.Service_Id = rate.Service_Id GROUP BY serv.Service_Id.

Yong Kevin · Answer

Hi Amit, I got it thank you very much

Yong Kevin · Answer

Hi Amit I tried the following codes: SELECT s.*, AVG(r.Score) FROM Services s LEFT JOIN Rating r ON r.Services_Id = s.Services_Id WHERE s.Service_Status = 'active' GROUP BY s.Services_id,r.Services_Id Is still not working and it shows an error message: Column 'Services.Service_Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Please help thank you

Yong Kevin · Answer

But i wanted to display services that have and don't have the rating scores Now, i try to use GROUP BY, but it can only display the Service rating score but not all the columns in the service table

Laxmidhar Sahoo · Answer

hi I think your solution is sum(r.Score)/count(r.Services_Id)