CODEWITHSUNDEEP
mysql
harunB10
harunB10

Reputation: 5207

How to create column which automatically calculates average from another table's columns

I'm creating a web application where I would display table with name of the company and their rating according to users. It would be much easier for me if there would be a column with average rating for all companies which automatically updates when new entry is made. For example, let say that I have Companies table:

╔════╤═════════════════════╤═══════════════╗
║ id │ nameOfTheCompany    │ owner         ║
╠════╪═════════════════════╪═══════════════╣
║ 1  │ Computer Repair LLC │ John Doe      ║
╟────┼─────────────────────┼───────────────╢
║ 2  │ Unimatrix           │ Barrack Obama ║
╚════╧═════════════════════╧═══════════════╝

And Services table where every service is evidented and rated by users (idCompany is FK).

╔════╤═══════════╤═══════════════════════════════════════════════╤═══════════╗
║ id │ idCompany │ serviceType                                   │ userGrade ║
╠════╪═══════════╪═══════════════════════════════════════════════╪═══════════╣
║ 1  │ 1         │ Installing antivirus and antispyware software │ 3         ║
╟────┼───────────┼───────────────────────────────────────────────┼───────────╢
║ 2  │ 2         │ Replacing new HDD                             │ 4         ║
╟────┼───────────┼───────────────────────────────────────────────┼───────────╢
║ 3  │ 1         │ Replacing new RAM                             │ 1         ║
╟────┼───────────┼───────────────────────────────────────────────┼───────────╢
║ 4  │ 2         │ CPU Cleaning                                  │ 5         ║
╚════╧═══════════╧═══════════════════════════════════════════════╧═══════════╝

I want to make new column in first table to have this:

╔════╤═════════════════════╤═══════════════╤═══════════════╗
║ id │ nameOfTheCompany    │ owner         │ averageRating ║
╠════╪═════════════════════╪═══════════════╪═══════════════╣
║ 1  │ Computer Repair LLC │ John Doe      │ 2             ║
╟────┼─────────────────────┼───────────────┼───────────────╢
║ 2  │ Unimatrix           │ Barrack Obama │ 4.5           ║
╚════╧═════════════════════╧═══════════════╧═══════════════╝

Upvotes: 1

Views: 71

Answers (2)

Faizan Younus
Faizan Younus

Reputation: 803

This query will have you result.

    select  c.*, avg(userGrade) as averageRating from table.company c join table.service s on c.id=s.idCompan group by s.idCompany;

You also can create view using this query.

Hope this helps

Upvotes: 1

Abhay Saraf
Abhay Saraf

Reputation: 1212

You can set a trigger when the event of adding a record to Services table. The trigger could cause a stored procedure to execute and update the averageRating in the Companies table.

Upvotes: 0

Related Questions