Home Postgres: avoid listing all columns (“must appear in the GROUP BY clause or be used in an aggregate function”)

Questions

Postgres: avoid listing all columns (“must appear in the GROUP BY clause or be used in an aggregate function”)

June 18, 2022

This query works:

SELECT
  COUNT(video_views.user_id) AS view_count,
  video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN video_user_interaction video_views ON (video_views.video_id = video_feed_unscored.id)
WHERE video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
GROUP BY
  video_feed_unscored.id,
  video_feed_unscored.title,
  video_feed_unscored.username,
  video_feed_unscored.user_id,
  video_feed_unscored.video_rating_id,
  video_feed_unscored.mux_asset_id,
  video_feed_unscored.mux_playback_id,
  video_feed_unscored.days_old,
  video_feed_unscored.view_start_count,
  video_feed_unscored.view_5seconds_count,
  video_feed_unscored.like_count
;

but it’s very explicit and I’d like it simplified to:

SELECT
  COUNT(video_views.user_id) AS view_count,
  video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN video_user_interaction video_views ON (video_views.video_id = video_feed_unscored.id)
WHERE video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
GROUP BY
  video_feed_unscored.id
;

But this gives the error column "video_feed_unscored.title" must appear in the GROUP BY clause or be used in an aggregate function.

Any other way to simplify the query?

>Solution :

Maybe not simplify but assuming, that there is a lot of feeds, and user see only few of them, query below should have better execution plan (it’s worth to check).

Additionaly selecting columns any range of columns is no longer a problem

SELECT
  view_count,
  video_feed_unscored.*
FROM video_feed_unscored
LEFT JOIN (
   select 
      count(*) view_count, 
      b.video_id 
   from video_user_interaction b
   where 
      b.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
   group by b.video_id
) c on (c.video_id = video_feed_unscored.id)

And there is always place for subquery (if execution plan is good enough)

SELECT
  (SELECT 
    COUNT(video_views.user_id)
   FROM video_user_interaction video_views
   WHERE 
      video_views.user_id = '8601ab73-d742-429e-b8e3-ba349725e5f5'
      AND video_views.video_id = video_feed_unscored.id
  ) AS view_count,
  video_feed_unscored.*
FROM video_feed_unscored