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