How to divide two values from the same column but at different rows

Question

I have a table like this:

postcode | value | uns
AA       | 10    | 51
AB       | 20    | 78
AA       | 20    | 78
AB       | 50    | 51

and I want to get a result like:

AA | 0.5 
AB | 2.5

where the new values are the division for the same postcode between the value with uns = 51 and the value with uns = 78. How can I do that with Postgres? I already checked window functions and partitions but I am not sure how to do it.

window functions partition by sounds very good here - show us your code — Vao Tsun, May 05 '17 at 14:36
Is `(postcode, uns)` unique? If that's the case, you just need a self-join. EDIT: If that's not the case, please describe what to do with duplicated rows. (I.e. do you want multiple divisions?) — pozs, May 05 '17 at 14:37
@pozs yes I just realised that they are unique (i have more columns on the real table) — Randomize, May 05 '17 at 14:39
What if you had another row with ("AA", 100, 52)? What would the results be? — fafl, May 05 '17 at 14:40
That row doesnt matter in that case, only for that specific uns values — Randomize, May 05 '17 at 14:41

pozs · Accepted Answer · 2017-05-05T14:46:42.637

2

If (postcode, uns) is unique, all you need is a self-join:

select postcode, uns51.value / nullif(uns78.value, 0)
from   t uns51
join   t uns78 using (postcode)
where  uns51.uns = 51
and    uns78.uns = 78

If the rows with either t.uns = 51 or t.uns = 78 may be missing, you could use a full join instead (with possibly coalesce() to provide default values for missing rows).

edited May 05 '17 at 14:46

answered May 05 '17 at 14:41

pozs

34,608
5
57
63

how can I skip the value if there is a `division by zero`? – Randomize May 05 '17 at 14:46
1

@Randomize [use `nullif()`](https://www.postgresql.org/docs/current/static/functions-conditional.html#FUNCTIONS-NULLIF) -- see the updated my answer – pozs May 05 '17 at 14:47

Erwin Brandstetter · Answer 2 · 2022-12-28T06:10:18.150

pozs' solution is nice and simple, nothing wrong with it. Just adding two alternatives:

1. Correlated subquery

SELECT postcode
     , value / (SELECT NULLIF(value, 0) FROM t WHERE postcode = uns51.postcode AND uns = 78)
FROM   t uns51
WHERE  uns = 51;

For only one or a few rows.

2. Conditional aggregate

SELECT postcode
     , min(value) FILTER (WHERE uns = 51)/ NULLIF(min(value) FILTER (WHERE uns = 78), 0) 
FROM   t
GROUP  BY postcode;

May be faster when processing most or all of the table.
Can also deal with duplicates per (postcode, uns), use an aggregate function of your choice to pick the right value from each group. For just one row in each group, min() is just as good as max() or sum().
About the aggregate FILTER:

Aggregate columns with additional (distinct) filters

I found the conditional aggregate approach very useful in a use case where I had a campaign ID instead of a postCode, and an event type instead of a "uns", especially since not all campaigns had all event types. — Mitchell Tracy, Dec 28 '22 at 03:27

How to divide two values from the same column but at different rows

2 Answers2

1. Correlated subquery

2. Conditional aggregate