php - SQL - 使用连接表进行 SUM 和 AVG 查询时遇到问题

Question

我有两个表，一个是评级，第二个是property_listing，我正在尝试根据此字段安全、娱乐、 parking 、公共(public)交通、社区中心进行 SUM 和 AVG我创建了这个 SQL 查询:

SELECT property_listing.*, coalesce( avg( rating.safety + rating.entertainment + rating.parking + rating.public_transportation + rating.community_centers )/5 , 0 ) AS rate
FROM property_listing
JOIN rating ON property_listing.property_id = rating.property_id
ORDER BY property_listing.property_id DESC

但是运行此 SQL 后我只得到一行。为什么我无法使用 AVG 获取其他记录？

我的 SQL fiddle :http://sqlfiddle.com/#!2/b2ab0/2

[Table 1: rating]

review_id property_id user_id safety entertainment parking public_transportation community_centers
--------- ----------- ------- ------ ------------- ------- --------------------- -----------------
8         553         24      2      4             5       5                     5
9         552         24      3      4             5       5                     5
10        550         24      3      4             5       5                     5
11        523         24      2      4             5       5                     5


[Table 2: property_listing]

property_id title                           user_id property_type_id
----------- ------------------------------- ------- ----------------
523         Bograshov St Tel Aviv Israel IL 48      16
550         Bograshov St Tel Aviv Israel IL 54      13
552         Bograshov St Tel Aviv Israel IL 54      16
553         Bograshov St Tel Aviv Israel IL 48      16

[Wanting this result with AVG]

property_id title                           user_id property_type_id rating
----------- ------------------------------- ------- ---------------- ------
523         Bograshov St Tel Aviv Israel IL 48      16               4.2
550         Bograshov St Tel Aviv Israel IL 54      13               4.4
552         Bograshov St Tel Aviv Israel IL 54      16               4.4
553         Bograshov St Tel Aviv Israel IL 48      16               4.2

Answer 1

尝试使用group by子句，因为avg()是一个聚合函数，您需要按列对结果进行分组:

SELECT property_listing.*,
       COALESCE(Avg(rating.safety + rating.entertainment
                    + rating.parking
                    + rating.public_transportation
                    + rating.community_centers) / 5, 0) AS rate
FROM   property_listing
       JOIN rating
         ON property_listing.property_id = rating.property_id
GROUP  BY property_listing.property_id
ORDER  BY property_listing.property_id DESC