SQL：在多个选定的表上使用AVG函数，但仅针对一列，并按该列分组

I am trying to run an SQL query. I've joined multiple tables using the JOIN function so that I have all the data I need in columns (and only those I need).

我正在尝试运行SQL查询。我使用JOIN函数加入了多个表,这样我就可以获得列中所需的所有数据(只有我需要的数据)。

This function works:

这个功能有效:

SELECT city, score FROM guests
JOIN delighted_surveys ON name = customer_name  
JOIN reservations ON guests.id = reservations.guest_id  
JOIN listings ON reservations.listing_id = listings.id 
JOIN properties ON listings.property_id = properties.id  
JOIN postal_addresses ON properties.id = postal_addresses.postally_addressable_id   
AND postal_addresses.postally_addressable_type = 'Property'

So essentially, I'm pulling the columns "CITY" and "SCORE" from the table "Guests" and a host of other joined tables. This works.. I get all the different scores for each city listed out.

基本上,我从“Guest”表和许多其他连接表中拉出“CITY”和“SCORE”列。这有效..我得到了列出的每个城市的所有不同分数。

GOAL: What I want to get, however, is the average score per city. I have tried using the AVG function, and I can get the average for the scores only. But I cannot get the average score per city. I keep getting syntax errors.

目标:然而,我想得到的是每个城市的平均得分。我尝试过使用AVG功能,我只能获得分数的平均值。但我无法得到每个城市的平均分数。我不断收到语法错误。

WHAT I'VE TRIED SO FAR: I've tried using "DISTINCT" and also "AVG" in conjunction with "GROUP BY" in multiple ways. I've also tried adding more "SELECT" statements in the query but that also results in syntax errors.

我做了多少尝试:我尝试过多次使用“DISTINCT”和“AVG”以及“GROUP BY”。我还尝试在查询中添加更多“SELECT”语句,但这也会导致语法错误。

Essentially, the results I'm getting look like this:

从本质上讲,我得到的结果如下:

   CITY       SCORE 
Oslo           9 
Copenhagen     10 
Oslo           8
Amsterdam      7
Copenhagen     6

and I want them to look like this, displaying the average for each city:

我希望它们看起来像这样,显示每个城市的平均值:

   CITY     AVERAGE SCORE
Copenhagen       8.0
  Oslo           8.5
Amsterdam         7

PS: Every single input has a data point (there is a score for each city in all cases).

PS:每个输入都有一个数据点(在所有情况下每个城市都有一个分数)。

Thanks in advance for any help with this! :)

在此先感谢您对此的任何帮助! :)

1 个解决方案

#1

Try this:

SELECT city, AVG(score) FROM guests
JOIN delighted_surveys ON name = customer_name  
JOIN reservations ON guests.id = reservations.guest_id  
JOIN listings ON reservations.listing_id = listings.id 
JOIN properties ON listings.property_id = properties.id  
JOIN postal_addresses ON properties.id = postal_addresses.postally_addressable_id   
AND postal_addresses.postally_addressable_type = 'Property'
GROUP BY city

You use your aggregate (AVG SUM or whatever) in the select, and all other fields must be in the group by. That is how you use aggregation, you do not need distinct.

您在select中使用聚合(AVG SUM或其他),所有其他字段必须在group by中。这就是你使用聚合的方式,你不需要区别。