Postgresql Calculate Difference Between Rows

PostgreSQL calculate difference between rows

This is what window functions are for:

select year, 
       month,
       fixes,
       fixes - lag(fixes) over (order by year, month) as increase,
from the_table;

For more details please see the manual:

http://www.postgresql.org/docs/current/static/tutorial-window.html

Calculate percentage difference between two rows

Use a Common Table Expression ("CTE") for the grouped result and join it to itself to calculate the difference to the previous season:

with summary as (
  select
    season,
    guildname,
    count(*) as mp_count, -- simplified equivalent expression
    count(*)/6 as grank -- simplified equivalent expression
  from mp_rankings
  group by season, guildname
)
select
  a.season,
  a.guildname,
  a.mp_count,
  a.grank,
  a.mp_count - b.mp_count as prev_season_percent_diff
from summary a
left join summary b on b.guildname = a.guildname
  and b.season = a.season - 1
where a.season = (select max(season) from summary)
order by a.grank desc

If you actually want a % in the result, concatenate a % to the difference calculation.

Calculate time difference over the first and the last table row

We can substract min(DateTimeStamp) from the max(DateTimeStamp)` and cast the difference as time.

select 
  cast(
     max(DateTimeStamp) 
   - min(DateTimeStamp)
     as time) TimeDiffernce 
from ImportLogs

| timediffernce |
| :------------ |
| 00:00:10      |

db<>fiddle here

Postgresql difference between rows

You can access the value of the "next" (or "previous") row using a window function. The concept of a "next" row only makes sense if you have a column to define an order on the rows. You said you have a date column on which you can order the result. I used the column name your_date_column for this. You need to replace that with the actual column name of course.

select id, 
       value, 
       lead(value, 1, 100) over (partition by id order by your_date_column) - value as change
from the_table
order by id, your_date_column

lead(value, 1, 100) says: take the column value of the "next" row (that's the 1). If there is no such row, use the default value 100 instead.

Calculate difference between current row and next available date value in SQL

It looks like you want the difference between each stoptime and the latest prior issue. If issues and stop times are properly interleaved, as you explained in the comments, then you can use window functions as follows:

select t.*, stop_time - max_issue as diff
from (
    select t.*, max(issue) over(order by issue) max_issue
    from mytable t
) t
where stop_time is not null

Calculate difference between the row counts of tables in two schemas in PostgreSQL

with counts as (
    select
        (select count(distinct id) from schema1.compound) as count_old,
        (select count(distinct id) from schema2.compound) as count_new
)
select
    'compound' as table_name,
    count_old,
    count_new,
    count_old - count_new as   diff
from counts;  

Calculate the only necessary difference and Group by between two Timestamps in PostgreSQL

try this :

SELECT age(max(ec."timestamp"), min(ec."timestamp")) - date_trunc('day', age(max(ec."timestamp"), min(ec."timestamp")))

Postgresql - Difference in value between two successive rows grouped by category

You can approach this using row_number():

select category,
       sum(case when seqnum = 1 then value else - value end) as diff
from (select t.*,
             row_number() over (partition by category order by timestamp) as seqnum
      from t
     ) t
where seqnum in (1, 2)
group by category
having count(*) > 1;

Difference two rows in a single SQL SELECT statement

Just select the two rows, join them into one, and subtract the values:

select d1.recdate, d2.recdate,
       (d2.col1 - d1.col1) as delta_col1,
       (d2.col2 - d1.col2) as delta_col2,
       ...
from (select *
      from dated_records
      where recdate = <date1>
     ) d1 cross join
     (select *
      from dated_records
      where recdate = <date2>
     ) d2

---

Related Topics