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?

Solution 1

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

Solution 2

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.

Solution 3

You can use MAX() trick on cars column.

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

Comments

[achabacha322]

Any idea if using max like that can cause performance issues?