PostgreSQL: How to Calculate Difference Between Rows

Often in PostgreSQL you may want to calculate the difference between consecutive rows in a table.

You can use the following basic syntax to do so:

SELECT year, 
       sales,
       sales - lag(sales) over (ORDER BY year) AS sales_diff
FROM sales_data;

This particular example selects the year and sales columns from the sales_data table, then creates a new column named sales_diff that calculates the difference in consecutive values of the sales column.

The following example shows how to use this syntax in practice.

Example: How to Calculate Difference Between Rows in PostgreSQL

Suppose that we create the following table named sales_data that contains information about the total products sold by a company during various years:

-- create table named 'sales'
CREATE TABLE sales_data (
  year INT  PRIMARY KEY,
  sales INT
);

-- add rows to the table 
INSERT INTO sales_data VALUES (2019, 54);
INSERT INTO sales_data VALUES (2020, 70);
INSERT INTO sales_data VALUES (2021, 77);
INSERT INTO sales_data VALUES (2022, 71);
INSERT INTO sales_data VALUES (2023, 90);
INSERT INTO sales_data VALUES (2024, 86);

-- view all rows in table
SELECT * FROM sales_data;

 year | sales 
------+-------
 2019 |    54
 2020 |    70
 2021 |    77
 2022 |    71
 2023 |    90
 2024 |    86

Suppose that we would like to calculate the difference between consecutive values in the sales column of the table.

We can use the following syntax to do so:

SELECT year, 
       sales,
       sales - lag(sales) over (ORDER BY year) AS sales_diff
FROM sales_data;

 year | sales | sales_diff 
------+-------+------------
 2019 |    54 |           
 2020 |    70 |         16
 2021 |    77 |          7
 2022 |    71 |         -6
 2023 |    90 |         19
 2024 |    86 |         -4

The resulting table contains a new column named sales_diff that shows the difference in sales between consecutive years.

For example, we can see:

• From 2019 to 2020, sales increased by 16.
• From 2020 to 2021, sales increased by 7.
• From 2021 to 2022, sales decreased by 6.

And so on.

Note that if you would like to calculate the difference between every n rows in a column, you can specify that value within the lag function.

For example, you could specify lag(sales, 3) to calculate the difference in sales between every three years.

Additional Resources

The following tutorials explain how to perform other common tasks in PostgreSQL:

PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to INSERT If Not Exists Already
PostgreSQL: How to SELECT DISTINCT on Multiple Columns