CODEWITHSUNDEEP
sqlpostgresqlaggregate-functionsdate-arithmetic
jordan
jordan

Reputation: 10742

Postgres - Calculate average monthly rows

I have a table that represents user's deposits into a savings account. I'd like to find the average number of deposits that a user makes per month but I'm having trouble getting there.

I've tried

SELECT date_part('month', timestamp) as month FROM savings_account_deposit
WHERE user_id = :user_id
AND savings_account_id = :savings_account_id
GROUP BY date_part('month', timestamp)

but I'm actually looking for a single number that represents the average deposits per month across the lifetime of the savings account.

Upvotes: 0

Views: 168

Answers (1)

GMB
GMB

Reputation: 222472

You would need another level of aggregation:

SELECT AVG(cnt) avg_deposit_per_month
FROM (
    SELECT COUNT(*) cnt 
    FROM savings_account_deposit
    WHERE user_id = :user_id AND savings_account_id = :savings_account_id
    GROUP BY date_trunc('month', timestamp)
) t

The subquery counts the number of deposits per month, then the outer query just averages the counts.

Note that I changed date_part in your query to date_trunc: the former would aggregate together deposits of the same month in different years, which is probably not what you want.

Upvotes: 2

Related Questions