CODEWITHSUNDEEP
databasepostgresqlaggregate-functions
sethvargo
sethvargo

Reputation: 26997

postgreSQL select additional columns that aren't used in aggregate function

I'm trying to write a query in PostgreSQL and I'm getting a little frustrated because it works in other database engines. I need to select the top 5 users from a given joins table like this:

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, yet PostgreSQL refuses to select additional fields that aren't used in aggregate functions. I'm getting the following error:

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

How can I do this in PostgreSQL??

Upvotes: 3

Views: 5226

Answers (4)

GlaIZier
GlaIZier

Reputation: 835

Just in case of somebody wants ANSI-92 standard solution and doesn't like 'Oracle' way to join 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;

Upvotes: 0

amitamb
amitamb

Reputation: 1067

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

Thus following will be final query

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 are using framework like rails then you can implement this easily with Model.column_names function.

Upvotes: 2

Scott Marlowe
Scott Marlowe

Reputation: 8900

Assuming that users.id IS a PK, then you can either

wait for 9.1

group by all fields

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

Upvotes: 2

Gerrat
Gerrat

Reputation: 29710

You could try: 

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

Upvotes: 9

Related Questions