Postgresql: sum of count and one column?

Question

I have a user_stats table. One of the columns in the table is driving_time.

user_stats
- user_id (FK)
- driving_time (Int)
- is_new_drive Bool
- is_time_duration Bool

Every time a user drives a new row is created and the is_new_drive is set to true. Every time a user ends a drive a new row is created with is_time_duration set to true and the driving_time set to the amount of seconds driven.

I want to run a query that returns:

drives_and_duration_total | user_id

drives_and_duration_total is the sum of counting all drives for a user and the amount of time driven for the user. So I want one number that is the sum of the number of is_new_drive == true records plus the sum of the driving_time column for a given user.

So, if user 10 drove 25 times and the sum of all the driving_time for the user is 23034 then the drives_and_duration_total should be 23059 (23034+25).

What query could allow me to get that type of table as a result (I would group by user_id...).

I've looked at this question:

How to compute the sum of multiple columns in PostgreSQL

but, I am not sure if and how this would lead me to the answer I need because I am not just computing the sum of columns, I am in need to compute the sum of one column and count the number of rows for is_new_drive=true case and then get the total...

Belayer · Accepted Answer · 2021-05-31 20:00:52Z

0

The referenced question asks and answers how to compute the sum of columns in the same row. This is not what you need instead you need the sum function. This computes the sum of values in the column. So:

select user_id, sum(driving_time) + 25 as drive_minuets
  from user_stats 
 group by user_id 
 where is_time_duration;

answered May 31, 2021 at 20:00

Belayer

15.1k2 gold badges13 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zumzum Over a year ago

So, I would need to use the dynamic row count instead of the hardcoded value 25. How would I do that?

Collectives™ on Stack Overflow

Postgresql: sum of count and one column?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related