CODEWITHSUNDEEP
sqlpostgresql
Dicky Perdian
Dicky Perdian

Reputation: 104

GROUP BY clause or be used in an aggregate function

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

Upvotes: 4

Views: 45074

Answers (2)

Tito
Tito

Reputation: 671

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!

Upvotes: 5

Kevin Böhmer
Kevin Böhmer

Reputation: 456

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

Upvotes: 4

Related Questions