harunB10 harunB10 - 4 months ago 9
SQL Question

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 ║
╚════╧═════════════════════╧═══════════════╧═══════════════╝

Answer

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