PostgreSQL - GROUP BY 子句或在聚合函数中使用 [英] PostgreSQL - GROUP BY clause or be used in an aggregate function

问题描述

我在这里找到了一些关于 SO 的主题，但我仍然找不到适合我的查询的设置.

I found some topics here on SO, but I still can't find the right setup for my query.

这是查询，在本地主机上运行良好:

This is query, that works me well on localhost:

@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 子句或在聚合函数中使用.

But on Heroku gives me the error above - GROUP BY clause or be used in an aggregate function.

然后我在某处读到，我应该指定表中的所有列，所以我尝试了这个:

Then I have read somewhere, that I should specify all columns in the table, so I tried this:

@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...

But this doesn't work on localhost and also not on Heroku...

查询的正确配置应该是什么?

What should be the right config of the query?

推荐答案

我认为您正在尝试在同一列上进行聚合和分组.这取决于你想要什么数据.以太这样做:

I think you are trying to aggregate and group by on the same column. It depends on what data you want. Ether do this:

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

或者你想数一数?然后像这样:

Or you want to count all maybe? Then like this:

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

这篇关于PostgreSQL - GROUP BY 子句或在聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！