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

Question

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??

I do believe it works in MySQL and SQLite but the "etc" is wrong. No other database allows this. Those are the only two. — user330315
– user330315, Commented Jan 21, 2011 at 8:02
Actually, assuming that users.id is a PRIMARY KEY, it is not wrong. (Though MySQL for example does it both when it's right and when it's wrong). PostgreSQL 9.1 will support running this query the way it is written - since the GROUP BY is on the PRIMARY KEY, we can infer that all the other columns are functionally dependent on it. — Magnus Hagander
– Magnus Hagander, Commented Jan 21, 2011 at 9:58
@Magnus: I know that 9.1 will support this, but 9.1 is currently not available — user330315
– user330315, Commented Jan 21, 2011 at 14:26
@horse: absolutely true. But the statement that they are wrong is partially (though only partially) incorrect. — Magnus Hagander
– Magnus Hagander, Commented Jan 21, 2011 at 15:29
@MagnusHagander: Do you know why a PRIMARY KEY is required and not mere UNIQU-ness? I cannot imagine a case where uniqueness would not be good enough. — A.H.
– A.H., Commented Mar 31, 2012 at 22:33

Gerrat · Accepted Answer · 2011-01-21 14:00:20Z

9

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

edited Jan 21, 2011 at 14:00

answered Jan 21, 2011 at 5:49

Gerrat

29.9k9 gold badges78 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user330315 Over a year ago

+1 for a cross-dbms solution. But the comma after users in the first line is wrong and you should order by num_deals DESC

araqnid Over a year ago

can't you remove the reference to the 'users' table in the subquery? that way it will only look at each table once.

Gerrat Over a year ago

@a_horse_with_no_name: You're correct about the ORDER BY. ...and at first I thought you were correct about the comma in the first line, but I think it is actually correct (it's separating the users table from the subquery/a table)

Gerrat Over a year ago

@araqnid: I've implemented your suggestion. Thanks.

user330315 Over a year ago

@Gerrat: ah, you are right about the comma. I'm not used to that old-style joins, I always use JOIN ... ON syntax. Sorry for the noise

|

Scott Marlowe · Accepted Answer · 2011-01-24 04:44:31Z

2

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

answered Jan 24, 2011 at 4:44

Scott Marlowe

9,0403 gold badges27 silver badges21 bronze badges

2 Comments

satels Over a year ago

with use max() - it low speed

Scott Marlowe Over a year ago

The use distinct on: select distinct on (col1, col2) col1, col2, col3, col4 from yada;

amitamb · Accepted Answer · 2012-03-31 16:12:13Z

2

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.

answered Mar 31, 2012 at 16:12

amitamb

1,06711 silver badges22 bronze badges

Comments

GlaIZier · Accepted Answer · 2016-03-29 07:38:48Z

0

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;

edited Mar 29, 2016 at 7:38

answered Mar 28, 2016 at 19:13

GlaIZier

86510 silver badges13 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related