CODEWITHSUNDEEP
sqlpostgresql
Jake McAllister
Jake McAllister

Reputation: 1075

Group by one column and sum the other

I this a query

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'

That returns this

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

I would like to return this 

user_id   state   duration
1         call    40
1         wait    50

I've tried to adding a GROUP BY user_state_logs.state

But I get this error

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

So two questions:

Why am i getting this error?

How could i get the query to return the data in the second format?

Upvotes: 2

Views: 1710

Answers (3)

PatBusz
PatBusz

Reputation: 46

You get error, because define select list when you use group by statement. Try this:

SELECT user_id, state, 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_id, state, duration

or

SELECT user_id, state, max(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_id, state

Upvotes: 0

Uriil
Uriil

Reputation: 12618

You should group by user_id and state:

SELECT user_id, state, sum(duration)
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_id, state

Upvotes: 1

Robert
Robert

Reputation: 25753

Try to do it that way:

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

Upvotes: 3

Related Questions