I am attempting to write a question in PostgreSQL and I am obtaining a little frustrated since it works in other database engines. I have to choose the very best 5 customers from the given joins table such as this:


Choose customers.*,

       COUNT(deals.id) AS num_deals

FROM customers, deals

WHERE deals.customers_id = customers.id

GROUP BY customers.id

ORDER BY num_deals LIMIT 5

I want the very best 5 customers. This code works in sqlite, mysql, etc, yet PostgreSQL will not choose additional fields that are not utilized in aggregate functions. I am obtaining the following error:

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

How do i do that in PostgreSQL??

You could attempt:

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

Presuming that customers.id Is really a PK, you'll be able to either

watch for 9.1

group by all fields

make use of an aggregate (i.e. max() ) on all fields