GROUP BY子句或在聚合函数中使用 [英] GROUP BY clause or be used in an aggregate function

问题描述

我想按 sender_id 分组，但是出现以下错误：

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

列 users.first_name必须出现在GROUP BY子句中或在聚合函数中使用

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

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

预期输出：

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

推荐答案

问题是您正在尝试使用分组依据函数不具有诸如count或sum之类的合计函数。在这种情况下Group By不会工作。但是要显示一个简单的示例，就好像

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

另外，我建议不要在'from'中使用另一个表，因为如果它们之间没有联接，那么您将只拥有一个包含所有这些字段的表两个表，并且数据之间没有真正的关联。

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!

这篇关于GROUP BY子句或在聚合函数中使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！