CODEWITHSUNDEEP
sqlpostgresqlaverageunpivot
wtwtwt
wtwtwt

Reputation: 388

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?

Upvotes: 1

Views: 1219

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

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

Upvotes: 1

GMB
GMB

Reputation: 222582

You can unpivot, and then compute the difference between the two conditional averages of each group:

select x.category, 
    abs(avg(t.value) filter(where not t.iscool) - avg(t.value) filter(where t.iscool)) diff
from mytable t
cross join lateral (values (town), (season)) as x(category)
group by x.category

If you want to be able to order the resultset as showed in the desired results, then we need to keep track of the original column:

select x.category, 
    abs(avg(t.value) filter(where not t.iscool) - avg(t.value) filter(where t.iscool)) diff
from mytable t
cross join lateral (values (town, 1), (season, 2)) as x(category, grp)
group by x.category, x.grp
order by x.grp

Demo on DB Fiddle:

category |                   diff
:------- | ---------------------:
TownB    |     0.5000000000000000
TownA    | 2.00000000000000000000
winter   |     3.0000000000000000
spring   |     3.0000000000000000

Upvotes: 1

Related Questions