user1575921 user1575921 - 7 months ago 46
SQL Question

select row_to_json table, error: must appear in the GROUP BY clause or be used in an aggregate function

I get this message

column "mi.*" must appear in the GROUP BY clause or be used in an aggregate function
what does that means? and how to solve it?

I tried change
GROUP BY m.id
to
GROUP BY m.id, mi.media_id
still same error

I test this if I remove
GROUP BY m.id ORDER BY COUNT(mua.id)
it works

data structure

[ { id: 54,
media_information: {
id: 1,
media_id: 54
}
},
]


query

SELECT
m.*,
row_to_json(mi.*) as media_information
FROM media m
LEFT JOIN media_information mi ON mi.media_id = m.id

LEFT JOIN media_user_action mua ON mua.media_id = m.id

GROUP BY m.id
ORDER BY COUNT(mua.id)
...


table

media
id | ...
1

media_information
id | media_id fk media.id | ...
1 | 1

media_user_action
id | media_id fk media.id | user_id
1 | 1 | 1
2 | 1 | 3


UPDATE base on below anser

Select m2.*
From media m2
LEFT JOIN media_user_action mua ON mua.media_id = m2.id

Where m2.id in (
SELECT
m.*,
row_to_json(mi.*) as media_information
FROM media m
LEFT JOIN media_information mi ON mi.media_id = m.id
)

GROUP BY m2.id
ORDER BY COUNT(mua.id)

Answer

You can get the grouping first and then do a join like

SELECT
  m.*,
  row_to_json(mi.*) as media_information
  FROM media m 
  LEFT JOIN media_information mi ON mi.media_id = m.id

  LEFT JOIN (select media_id, COUNT(id) as mua_count
             from media_user_action
             group by media_id) xxx ON xxx.media_id = m.id

  ORDER BY xxx.mua_count;
Comments