Postgres aggregate problem

Question

I'm not sure why this won't work:

SELECT u.id, u.tag, u.unit_type, Count(p.id) AS num_points
FROM ot2.unit u
INNER JOIN ot2.point p on p.unit_id = u.id
GROUP BY u.id
HAVING Count(p.id) > 800;

ERROR: column "u.tag" must appear in the GROUP BY clause or be used in an aggregate function SQL state: 42803

I have already provided a column for group by.

This won't work in all RDBMSs except MySQL. The error message tells you all you need to know! It doesn't ask for "a column". It tells you the exact additional one you need. You will also need to sort out u.unit_type — Martin Smith
– Martin Smith, Commented Aug 17, 2011 at 23:22

Eddy · Accepted Answer · 2011-08-17 23:23:42Z

2

Just what the error says. u.tag is not in the group by and you aren't telling how to aggregate the different u.tag values to a single one.

More concrete you have 2 options:

Add u.tag in the group by clause (and u.unit_type as well)
use an aggregate function on those 2 fields (min, max, sum, avg, ....)

answered Aug 17, 2011 at 23:23

Eddy

5,38026 silver badges41 bronze badges

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

2 Comments

tumultous_rooster Over a year ago

Why? This explains nothing past a restatement of the error message.

Eddy Over a year ago

By definition: if you have a group by statement every field in the select list either has to be named in the group by statement or has to have a aggregate function. In this case u.id is in the group by and (p.id) has an aggregate function count(). u.tag and u.unit_type are in neither.

Collectives™ on Stack Overflow

Postgres aggregate problem

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related