Get the Average Score and display

Yong Kevin

5y1.2kLearn iOS Programming

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

8 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Gupta5y ago
Accepted 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
+3
satheesh d4y ago
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.
+1
Yong Kevin5y ago
Hi Amit,

I got it thank you very much
+1
Amit Gupta5y ago
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'
+2
Yong Kevin5y ago
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
+1
Amit Gupta5y ago
Use left join (services table) and based on serviceid fetch the avg from ratings table.
+2
Yong Kevin5y ago
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
+1
Laxmidhar Sahoo5y ago
hi

I think your solution is

sum(r.Score)/count(r.Services_Id)
+1