How to get difference between two average values in PostgreSQL, where the averages are on a column, and the final table grouped by two columns?

I want to know the difference between two averages of value, where each average is filtered by a condition isCool to be either True or False, and the final result grouped by town and season, e.g.

table

| id | value | isCool | town   | season |
|----|-------|--------|--------|--------|
| 0  | 1     | True   | TownA  | spring |
| 1  | 2     | False  | TownA  | winter |
| 2  | 3     | True   | TownB  | spring |
| 3  | 4     | False  | TownA  | winter |
| 4  | 5     | False  | TownB  | spring |
| 5  | 6     | True   | TownB  | winter |

I want to end up with the table:

| category | difference_of_is_cool_averages |
|----------|--------------------------------|
| TownA    | 2                              | <-- ABS(1 - (2 + 4)/2)
| TownB    | 0.5                            | <-- ABS(5 - (3 + 6)/2)
| spring   | 3                              | <-- ABS(5 - (3 + 1)/2)
| winter   | 3                              | <-- ABS(6 - (4 + 2)/2)

I have attempted this but my PostgreSQL skills are limited and I did not get far at all, unfortunately. I tried

SELECT
   AVG(value), town
   (SELECT id, value, town, season
   FROM table
   WHERE isCool = 'True') AS TableSummary1
GROUP BY town;

but this is far from what I want. Is someone able to help please? Is this even possible with PostgreSQL?

Solution

Here Union All will help you. Simply calculate the difference of average once by grouping town then by grouping season and union them. You can write your query like below:

select
town "Category",
round(abs(avg(value) filter (where iscool='t') - avg(value) filter (where iscool='f')),2) "difference_of_is_cool_averages"
from town
group by town

union all

select
season,
round(abs(avg(value) filter (where iscool='t') - avg(value) filter (where iscool='f')),2)
from town
group by season

DEMO