Comparing 2 query result in a query PostgreSQL

Question

I'm wondering if it's possible to compare 2 query result into one in PostgreSQL. For example: I have this data on the whole month of March then i have another data for the whole month of april.

This query is the one I'm using to get the data on the month of March:

    SELECT availability_date, ROUND(AVG(availability_percentage),2)
FROM dashboard.availability
WHERE availability_date BETWEEN '2020-03-01' AND '2020-04-01'
GROUP BY availability_date
ORDER BY availability_date ASC

Then this is the one I'm using to get the data on the month of April:

SELECT availability_date, ROUND(AVG(availability_percentage),2)
FROM dashboard.availability
WHERE availability_date BETWEEN '2020-04-01' AND '2020-05-01'
GROUP BY availability_date
ORDER BY availability_date ASC

Is it possible for me to combine them to one data so I can display the result on both month? For example:

Month     percentage
March      100%
February   85%

Tim Biegeleisen · Accepted Answer · 2020-04-29 07:12:27Z

1

Yes, use conditional aggregation:

SELECT
    ROUND(AVG(availability_percentage)
       FILTER (WHERE availability_date BETWEEN '2020-03-01' AND '2020-04-01') , 2) AS avg_march,
    ROUND(AVG(availability_percentage)
       FILTER (WHERE availability_date BETWEEN '2020-04-01' AND '2020-05-01'), 2) AS avg_april
FROM dashboard.availability
WHERE availability_date BETWEEN '2020-03-01' AND '2020-05-01';

Note that you should not be aggregating by date, because the averages you want to take span multiple dates.

edited Apr 29, 2020 at 7:12

answered Apr 29, 2020 at 6:55

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

1 Comment

Elijah Leis Over a year ago

This works if I used GROUP BY. But I think i should remove the ORDER BY clause. Also how can i ROUND the result? It threw a massive amount of decimals but if i used the ROUND function it throws an error that "FILTER specified, but round is not an aggregate function"

Collectives™ on Stack Overflow

Comparing 2 query result in a query PostgreSQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related