PostgreSQL-GROUP BY子句或在聚合函数中使用

Question

我在SO上找到了一些主题，但是仍然找不到适合我的查询的设置。

这是查询，在本地主机上运行得很好：

@cars = Car.find_by_sql('SELECT cars.*, COUNT(cars.id) AS counter 
                         FROM cars 
                         LEFT JOIN users ON cars.id=users.car_id 
                         GROUP BY cars.id ORDER BY counter DESC')

但是在Heroku上，我 遇到了-GROUP BY子句 上面的错误， 或者在聚合函数中使用 。

然后，我在某处读到，我应该指定表中的所有列，因此我尝试了以下操作：

@cars = Car.find_by_sql('SELECT cars.id, cars.name, cars.created_at, 
                                cars.updated_at, COUNT(cars.id) AS counter 
                         FROM cars 
                         LEFT JOIN users ON cars.id=users.car_id 
                         GROUP BY (cars.id, cars.name, cars.created_at, cars.updated_at) 
                         ORDER BY counter DESC')

但这在本地主机上不起作用，在Heroku上也不行…

什么是查询的正确配置？

Answer 1

我认为您正在尝试在同一列上进行汇总和分组。这取决于您想要什么数据。以太做到这一点：

SELECT 
 cars.name, 
 cars.created_at, 
 cars.updated_at, 
 COUNT(cars.id) AS counter 
FROM cars 
LEFT JOIN users 
  ON cars.id=users.car_id 
GROUP BY cars.name, cars.created_at, cars.updated_at 
ORDER BY counter DESC

还是您想数一数？然后像这样：

SELECT
 cars.id,
 cars.name, 
 cars.created_at, 
 cars.updated_at, 
 COUNT(*) AS counter 
FROM cars 
LEFT JOIN users 
  ON cars.id=users.car_id 
GROUP BY cars.id, cars.name, cars.created_at, cars.updated_at 
ORDER BY counter DESC