Re: How can I calculate differences between values - Mailing list pgsql-general

Hi;

also you can try this query which should work for any version

SELECT
    a.userid, a.data, max(f.data), a.data - max(f.data)
FROM
    (SELECT userid, data FROM foo EXCEPT SELECT userid, min(data) FROM foo GROUP BY userid) a LEFT JOIN foo f ON (f.userid = a.userid AND f.data < a.data) GROUP BY a.userid, a.data ORDER BY a.userid, a.data

A B wrote:

With 8.4's analytic capabilities you can do this:
select * from (
  select userid, data - lag(data) over (partition by userid order by data)
diff
    from foo) q
  where diff is not null;   

Thank you! That worked perfectly!