CODEWITHSUNDEEP
sqlsql-serveraveragecalculated-columns
Kristin Vernon
Kristin Vernon

Reputation: 155

SQL Server - How to get the average rating from another table and include it in current table?

I have a table of contractors like so:

SELECT [ContractorID]
  ,[Contractor]
  ,[BWSLVendorCode]
  ,[PhoneNumber]
  ,[Address]
  ,[PreQualification]
  ,[GSTNumber]
  ,[Avg] FROM Contractor

And a Feedback table that stores individual rating and comments for each contractor like so:

SELECT [ID]
  ,[ContractorID]
  ,[Rating]
  ,[Comments]
  ,[Timestamp] FROM Feedback

What I want to do is find the average rating of each individual contractor and include it in the contractor table under the Avg column. I thought about using a function and calling it in a computed column, but I can't seem to figure out how to write said function.

EDIT: The rating is between 1 - 5

Upvotes: 0

Views: 1714

Answers (3)

Mark He
Mark He

Reputation: 741

This will update the Avg column in the Contractor table with the average value of ratings of the contractor in the Feedback table.

;with feedback_grouped 
AS 
( 
SELECT f.ContractorID, AVG(CAST(f.rating as decimal(3,2))) as avg_rating
   FROM   Feedback f
   GROUP BY f.ContractorID
)
UPDATE Contractor
SET avg=avg_rating
FROM Contractor c
INNER JOIN feedback_grouped 
ON c.ContractorID = feedback_grouped.ContractorID

This groups each feedback by ContractorID and calculates the average Rating to update the original Contractor table

Ensure that the Avg column in Contractor table is a decimal datatype since the average might have decimal values. You can change the (CAST(f.rating as decimal(3,2))) to the same decimal datatype you are using in the Avg column.

Upvotes: 2

Sam CD
Sam CD

Reputation: 2097

EDIT

To automatically update the column value, you can use a trigger, which would look something like:

CREATE TRIGGER [DBName].[updateAvg]
ON [DBName].Contractors
AFTER UPDATE,INSERT
AS
BEGIN   

;WITH avgRates AS (
SELECT [ContractorID]
  ,SUM([Rating]) as totalRating
  ,COUNT(*) as noOfRatings
FROM Feedback
GROUP BY ContractorID
        )

UPDATE C
SET Avg = A.totalRating / A.noOfRatings
FROM Contractor C
INNER JOIN avgRates A
ON C.ContractorID = A.ContractorID

END

My syntax may be wrong on the CREATE TRIGGER, so you would want to reference the official documentation:

CREATE TRIGGER (Transact-SQL)

Upvotes: 2

Joe
Joe

Reputation: 335

How many "Contractors" are you expecting in your database? And how many Feedbacks per contractor? I ask this because calculating the Avg should be done at runtime so that you don't have to recalculate every time a new feedback is put in...

Instead of saving the Avg in the table, maybe you should have a view that calculates that and call the view instead of the table. Such as like this:

CREATE VIEW ContractorView AS SELECT C.*, ((SELECT SUM(F1.Rating) FROM Feedback as F1 WHERE F1.ContractorID = C.ContractorID) / (SELECT COUNT(F2.ID) FROM Feedback as F2 WHERE F2.ContractorID = C.ContractorID)) as Avg FROM Contractors as C

The syntax is something close to that. ;)

Upvotes: 0

Related Questions