PostgreSQL - GROUP BY clause or be used in an aggregate function

Question

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') 

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') 

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

What should be the right config of the query?

Answer 1

A query such as this (retrieving all or most rows) is faster if you GROUP before you JOIN. Like this:

SELECT id, name, created_at, updated_at, u.ct FROM   cars c LEFT   JOIN (     SELECT car_id, count(*) AS ct     FROM   users     GROUP  BY 1     ) u ON u.car_id  = c.id ORDER  BY u.ct DESC; 

This way you need far fewer join operations. And the rows of the table cars do not have to be first multiplied by joining to many users each and then grouped back to be unique again.
Only the right table has to be grouped, which makes the logic simpler, too.

Answer 2

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