sql - 按一列分组并对另一列求和

Question

这是一个查询

SELECT * FROM user_state_logs WHERE user_state_logs.user_id = 1
AND created_at BETWEEN '2015-05-03 11:06:05' AND '2015-05-13 11:06:05'

返回这个

user_id   state   duration
1         call    10
1         call    20
1         wait    30
1         call    10
1         wait    20

我想返回

user_id   state   duration
1         call    40
1         wait    50

我尝试添加一个 GROUP BY user_state_logs.state

但是我得到这个错误

ERROR: column "user_state_logs.user_id" must appear in the GROUP BY clause or be used in an aggregate function

所以两个问题:

为什么会出现这个错误？

我怎样才能让查询以第二种格式返回数据？

Answer 1

尝试这样做:

SELECT user_state_logs.state, user_state_logs.user_id, sum(user_state_logs.duration)  as duration
FROM user_state_logs 
WHERE user_state_logs.user_id = 1
AND created_at BETWEEN '2015-05-03 11:06:05' AND '2015-05-13 11:06:05'
GROUP BY user_state_logs.state, user_state_logs.user_id