GROUP BY clause or be used in an aggregate function

Question

I want to group by sender_id, but I get the following error:

column "users.first_name" must appear in the GROUP BY clause or be used in an aggregate function

SELECT users.first_name, users.last_name, users.avatar_url, users.age, chatting.content, chatting.sender_id, chatting.received_id, chatting.created_at as created_at_chatting
FROM users, chatting 
WHERE chatting.received_id = users.id 
  AND received_id='4' 
GROUP BY chatting.sender_id

tb_chatting

chatting_id | content | received_id | sender_id 
1           | hallo   | 4           | 5
2           | whoaa   | 4           | 6
3           | wow     | 4           | 5

tb_users

user_id | first_name  | last_name | age | avatar_url 
5       | dicky       | perdian   | 12  | httpxxxxxxx
6       | ferda       | est       | 13  | httpsxxxxxx

Expected output:

avatar_url | first_name | last_name | content 
httpxxxxxxx| dicky      | perdian   | hallo 
httpsxxxxxx| ferda      | est       | whoaa

This is an extremely common mistake / problem, but I can't find a nice reference answer at the minute. To understand what the error means, consider: if there is more than one user for a particular `chatting.sender_id`, how should the DBMS decide which one should appear in the output row? (You may "know" that there is no such case; the DBMS does not.) — IMSoP, May 16 '18 at 14:41
**RULE**: All retrieved fields that are not enclosed within a aggregate function (e.g. `sum`, `count', 'average`, etc.) **MUST** be included in the `GROUP BY` list. — FDavidov, May 16 '18 at 14:41
In postgre select field must appear in group by clause, I know it is painfull but it is also good practice, as @a_horse_with_no_name said, you don't have any aggregate function so why don't you just remove the group by clause? — Nerevar, May 16 '18 at 14:45
@Nerevar: Unrelated, but: it's [Postgres](https://wiki.postgresql.org/wiki/FAQ#What_is_PostgreSQL.3F_How_is_it_pronounced.3F_What_is_Postgres.3F) not "postgre" — , May 16 '18 at 14:50
This sounds like a typical [`greatest-n-per-group`](https://stackoverflow.com/questions/tagged/postgresql+greatest-n-per-group) problem — , May 16 '18 at 14:53
The join you have in your query will not return any rows as there is no user_id = 4 in your sample data (but that's the only id in `received_id`) — , May 16 '18 at 14:54
Possible duplicate of [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — IMSoP, May 16 '18 at 14:56

score 5 · Answer 1 · edited May 18 '18 at 12:39

The problem is you are trying to use Group By function without an aggregate function like count or sum. in this instance Group By wouldnt work. But to show a simple sample it would be like

select users.first_name, users.last_name, users.avatar_url, users.age,
count(chatting.content)`--If that field contains messages count is good enough
FROM users
  join chatting c on c.senderid = users.id -- if it exists
WHERE chatting.received_id = users.id AND received_id='4'
GROUP BY users.first_name, users.last_name, users.avatar_url, users.age

Also I would recommend not to use another table in 'from' because if you dont have a join between them you will simply have a table with all field of those two tables and no real correlation between the data.

I would recommend you learn how to build a database schema which will give you a better grasp of how tables are Adesigned so you can write a top notch query!

score 4 · Answer 2 · answered May 16 '18 at 14:44

Essentially you need to include every single column that gets selected in your group by in the right order.

For example:

SELECT users.first_name, 
       users.last_name, 
       users.avatar_url, 
       users.age, 
       chatting.content, 
       chatting.sender_id, 
       chatting.received_id, 
       chatting.created_at as created_at_chatting
FROM users, chatting 
WHERE chatting.received_id = users.id 
  AND received_id='4' 
GROUP BY 
       chatting.sender_id,
       users.first_name, 
       users.last_name, 
       users.avatar_url, 
       users.age, 
       chatting.content, 
       chatting.received_id, 
       chatting.created_at

It's working for me thanks ;) – Yassine ECHCHARAFI Mar 25 '22 at 08:09 — Yassine ECHCHARAFI, Mar 25 '22 at 08:09

GROUP BY clause or be used in an aggregate function

2 Answers2

Linked