CODEWITHSUNDEEP
postgresqlaggregate-functions
Andrew Fox
Andrew Fox

Reputation: 133

Grouped difference in PostgreSQL Query

This is fairly basic question, more around the existence of an aggregate "difference" function, or something equivalent in PostgreSQL. If I have the following structure to query :

id    | value
45572 | 802033.9692
45572 | 395904.2332
45573 | 750143.7240
45573 | 114797.8068
45574 | 357421.6152
45574 | 42223.3203
45576 | 13486.1753
45576 | 961121.1070
45571 | 289127.9147
45571 | 662589.0504
45579 | 900874.5207
45579 | 235575.6564

It's naturally a simple matter to aggregate the "value" column per "id" using a GROUP BY

select id, sum(value)
  from ...
 where ...
 group by id
 order by id

My question is, is there something equivalent aggregate function to find the absolute difference in the "value" rows with respect to each pair of id's. In this example, the result I'm looking for would look something like

id    | value
45572 | 406129.736
45573 | 635345.9172
45574 | 315198.2949
45576 | 947634.9317
45571 | 373461.1357
45579 | 665298.8643

In this instance, I'm guaranteed that there will be exactly 2 of each distinct id values. Is it a window function I should be using here? If so, I'm unsure whereabouts to begin with it. A pointer in the right direction of the most efficient way of doing this would be much appreciated.

Upvotes: 0

Views: 2061

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521629

You may use MIN/MAX here:

SELECT
    id,
    MAX(value) - MIN(value) AS diff
FROM yourTable
GROUP BY
    id;

Assuming that each id in your table has exactly a pair of records, then the above query should give you the output you want.

Upvotes: 2

Related Questions