PostgreSQL: calculating multiple averages in one query

Question

I'd like to calculate an average amount of money spent per day for the past 7, 30, 90 and 180 days. I know how to do it using PL/pgSQL, but I'd prefer to do it in one query if possible. Something like this:

SELECT SUM(amount)/days
FROM transactions
WHERE created_at > CURRENT_DATE - ((days || ' day')::INTERVAL)
AND days = ANY(ARRAY[7,30,90,180]);

ERROR: column "days" does not exist

redneb · Accepted Answer · 2016-09-22 16:41:55Z

1

You can use unnest to convert the array into a table and the use a correlated subquery to calculate the average:

SELECT
    days,
    (SELECT SUM(amount)/days
     FROM transactions
     WHERE created_at > CURRENT_DATE - ((days || ' day')::INTERVAL)
    ) AS average
FROM unnest(ARRAY[7,30,90,180]) t(days)

answered Sep 22, 2016 at 16:41

redneb

24.3k7 gold badges46 silver badges55 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL: calculating multiple averages in one query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related