PostgreSQL multiple column outputs using where clause

Question

I would like to find the average of a column for 2 time periods but I'm unsure how to display both in single query. I know the below won't work but I'm looking for the proper syntax:

SELECT
AVG(amount) AS avg_amt
FROM table
WHERE day >= date('2019-05-05') AND day<= date('2019-07-04'),
AVG(amount) AS avg_amt2
WHERE day >= date('2019-07-05') AND day<= date('2019-09-04')

I would like to repeat but for a different time period for comparison — paranormaldist
– paranormaldist, Commented Nov 18, 2020 at 15:39

GMB · Accepted Answer · 2020-11-18 15:40:17Z

3

You can use the FILTER clause to aggregate functions:

SELECT
    AVG(amount) FILTER(WHERE day >= date '2019-05-05' AND day <= date '2019-07-04') AS avg_amt1,
    AVG(amount) FILTER(WHERE day >= date '2019-07-05' AND day <= date '2019-09-04') AS avg_amt2
FROM table

We can optimize the query a little with a WHERE clause:

SELECT
    AVG(amount) FILTER(WHERE day <= date '2019-07-04') AS avg_amt1,
    AVG(amount) FILTER(WHERE day >= date '2019-07-05') AS avg_amt2
FROM table
WHERE day >= date '2019-05-05' AND day <= date '2019-09-04'

answered Nov 18, 2020 at 15:40

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Collectives™ on Stack Overflow

PostgreSQL multiple column outputs using where clause

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