PostgreSQL selects additional columns that are not used in the aggregate function

Question

I'm trying to write a query in PostgreSQL, and I'm a little upset because it works in other database engines. I need to select the 5 best users from this join table as follows:

SELECT users. *, 
       COUNT (deals.id) AS num_deals 
FROM users, deals 
WHERE deals.users_id = users.id 
GROUP BY users.id 
ORDER BY num_deals LIMIT 5;

I need the top 5 users. This code works in sqlite, mysql, etc., but PostgreSQL refuses to select additional fields that are not used in aggregate functions. I get the following error:

PGError: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

How to do it in PostgreSQL?

Answer 1

:

SELECT users.*, a.num_deals FROM users, (
    SELECT deal.id as dealid, COUNT(deals.id) AS num_deals 
    FROM deals 
    GROUP BY deal.id
) a where users.id = a.dealid
ORDER BY a.num_deals DESC
LIMIT 5

Answer 2

, users.id IS PK,

9.1

(.. max())

Answer 3

Another solution that works is to use all attributes implicitly in GROUP BY

So the next one will be the final request

SELECT users.*, 
       COUNT(deals.id) AS num_deals 
FROM users, deals 
WHERE deals.users_id = users.id 
GROUP BY users.id, users.name, users.attrib1, ..., users.attribN
ORDER BY num_deals LIMIT 5;

If you use the framework as rails, you can easily implement it using the Model.column_names function.

Answer 4

Just if someone wants a standard ANSI-92 solution and doesn't want Oracle to join the tables ...

SELECT users.*, num_deals
FROM users
JOIN
  (SELECT deals.users_id as users_id, count(deals.users_id) as num_deals
   FROM deals
   GROUP BY deals.id) grouped_user_deals
ON grouped_user_deals.users_id = users.id
ORDER BY num_deals DESC
LIMIT 5;