Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function]

Question

Im want to query distinct and count:

SELECT DISTINCT way.vehicle, count(way.vehicle), way.id, way.created_at, way.updated_at, way.version 
from waypoints as way 
left join dtb_vehicle on way.vehicle = dtb_vehicle.vehicle  
where (6373 * acos (cos ( radians(21.0277644)) * cos ( radians (way.latitude)) * cos (radians (way.longitude) - radians (105.8341598)) + sin (radians (21.0277644)) * sin (radians (way.latitude)))) < 20 
AND dtb_vehicle.vehicle_type = 600 
GROUP BY way.vehicle 
HAVING count(way.vehicle) > 10

But it always show error :column "way.id" must appear in the GROUP BY clause or be used in an aggregate function.

Please help me. Thanks

Then do as the error message suggests (if way.id is the primary key, then you only need to group by that). Btw: distinct together with group by rarely makes sense. Due to the group by you only get one row per vehicle anyway — a_horse_with_no_name

Jonathan Willcock Jonathan Willcock · Accepted Answer · 2020-02-27T06:54:55

This is just the first of four errors that you will get. PostreSQL stops checking your SQL once it hits an error, so it does not mean that there is only one. In fact you have the same problem with all of id, created_at, updated_at and version. As the error tells if you are using GROUP BY then all the columns in the SELECT statement must either be in the GROUP BY clause, or need to have some sort of aggregate function used on them. Assuming you do not want to add these to the GROUP BY (on the grounds that nothing will then presumably have a COUNT > 10), then you either have to drop them from the SELECT or apply some aggregate function. In your case MAX might be suitable, but without knowing more I cannot really tell.

Postgres error [column must appear in the GROUP BY clause or be used in an aggregate function]

1 Answers