CODEWITHSUNDEEP
postgresqlaggregate-functions
Broono
Broono

Reputation: 111

Postgresql: computing AVG between rows with given IDs

I am using Postgresql. Let's say I have a transactions_table with just two columns:
- aggregated month number (integer)
- value of transactions for given month (float)

Tricky part: for some months there have been no transactions. Such months are NOT in table at all. I.e. I might have data for aggregated month number 1, 2, 7, 9 (no row for month 3, 4, 5, 6, 8) etc.

I need to make a new averages_table, with average transactions value from previous 12 months for each month in transactions_table (obviously, without aggregated months < 12).
I need average for previous 12 months, not previous 12 rows. How can I do that?

Upvotes: 1

Views: 46

Answers (1)

klin
klin

Reputation: 121494

It seems you should limit the data to desired period in a derived table (a subquery in the FROM clause) and calculate averages on the limited dataset, like this:

select month, product_id, avg(transaction_value) 
from (
    select month, product_id, transaction_value 
    from transactions
    where month between 1806 - 12 and 1806
    ) s
group by month, product_id

where 1806 is the last month of the desired period (probably a query parameter).

Upvotes: 1

Related Questions