查找平均SQL [英] Finding an average SQL

问题描述

我正在尝试创建评分的平均值.

im trying to create an average on a rating.

我尝试了AVG(column_name).但是，这样做的问题是，它对所有收视率求和并给出平均值.

i have tried the AVG(column_name). however the problem with this is it does a total for all the ratings and gives the average.

Select Company.company_id, company.Company_Name, avg(UserJobRating.Total_Rating)
from company
inner join UserJobRating on Job_id = UserJobRating.Job_ID
inner join jobs on jobs.Company_id = company.company_id
group by company.company_id

例如，我要说的是，一家专业公司有10/15个职位，每个职位都有职位等级.我希望它将特定于该公司的工作归为一组，并对该公司的所有工作给出平均评分.

what i want is say for example, a spefic company has 10/15 jobs each with a job rating. i want it to group together the jobs specific to that company and give an average rating for the company over all their jobs.

要执行此操作，我需要选择哪种类型?

what type of select would i need to do in order to do this?

推荐答案

我要从您的评论中猜测一下发生的事情，所以我认为您的 JOIN 有点过时了.试试这个:

I'm going to guess a little bit on what's going on from your comment, so I think your JOIN is off a bit. Try this:

Select Company.company_id, company.Company_Name, avg(UserJobRating.Total_Rating)
from company
  inner join jobs on jobs.Company_id = company.company_id
  inner join UserJobRating on jobs.Job_id = UserJobRating.Job_ID
group by company.company_id

• SQL小提琴演示

在没有看到表定义的情况下，这只是一个猜测，但是我敢打赌，您正在使用 UserJobRating 表通过以下语句生成一个 Cartesian Product :

Without seeing your table definitions, this is a bit of a guess, but I would bet you're producing a Cartesian Product with the UserJobRating table with this statement:

on Job_id = UserJobRating.Job_ID

这篇关于查找平均SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！