Calculate percentage change between 2 columns

Question

I have a table that has 2 columns that list the amount of a specific item per release of our product. I need to calculate the percentage increase between the two columns and append it to a new column in the table, however I have not found any documentation that relates to this? I'm using Postgres 9.0, and I need to examine the percentage increase between the two columns as a part of the QC process to ensure there is no missing/wrong data between releases.

Here's the table definition:

oid oid[] NOT NULL,
"State" character varying(2)[] NOT NULL,
release_1121 numeric NOT NULL,
release_1122 numeric NOT NULL,
CONSTRAINT oid PRIMARY KEY (oid)

I'd like to add a percentage increase column and populate it with the correct percentages.

1121, 1122 — those are fields names or values? Please, post a complete table definition here. And show us what you have done so far. — vyegorov, May 10 '12 at 14:26
Seems like there should be a `release` table with *one* amount per release and a view / function computing the change between all / selected releases. **Not** a table for the delta between releases. Also, please mention your version of Postgres and the intended purpose of the calculation (to better understand what you need). — Erwin Brandstetter, May 10 '12 at 14:41

Erwin Brandstetter · Answer 1 · 2019-03-04T13:02:04.250

I think this is what you actually need:

Table should look something like this:

CREATE TABLE release (
release_id integer PRIMARY KEY,    -- pk is NOT NULL automatically
-- state varchar(2)[] NOT NULL,    -- ??
amount numeric NOT NULL
);

Test data:

INSERT INTO release VALUES (release_id, amount)
  (1121, 25)
, (1122, 30)
, (1123, 90)
, (1124, 10);

Query:

WITH x AS (
    SELECT *
         , lag(amount) OVER (ORDER BY release_id) as last_amount
    FROM   release
    )
SELECT release_id, amount
     , (amount - last_amount) AS abs_change
     , round((100 * (amount - last_amount)) / last_amount, 2) AS percent_change
FROM   x
ORDER  BY release_id;

CTE (WITH clause) and window function lag() require PostgreSQL 8.4 or later.
Result:

release_id | amount | abs_change | percent_change
-----------+--------+------------+---------------
1121       | 25     | <NULL>     | <NULL>
1122       | 30     | 5          |  20.00
1123       | 90     | 60         | 200.00
1124       | 10     | -80        | -88.89

Don't use oid as primary key! That is bad practice. In PostgreSQL 9.0 WITHOUT OIDS is the default. Read more here.
Don't use mixed case identifiers like "State" if you can avoid it. See:
- Are PostgreSQL column names case-sensitive?

This is just *exactly* what I was thinking of suggesting before I got to your answer! No redundant storage of amounts, so if one amount is found to be incorrect, you fix that one amount and you're done; there is no need to hunt down redundant copies or recalculate dependent values. — kgrittn, May 10 '12 at 18:25
@kgrittn: Same idea, probably because it is the obvious path to avoid redundant storage. :) — Erwin Brandstetter, May 10 '12 at 20:45

score 2 · Accepted Answer · answered May 10 '12 at 14:30

I would say, that adding percentage increase column is a one time operations, that can be done like this (details in the docs):

ALTER TABLE target ADD pct float;

And then you can update the table, populating it with new values:

UPDATE target SET pct = (after::float - before::float) / before::float;

Calculate percentage change between 2 columns

2 Answers2