Question 89129 in PostgreSQL Question posted in
The official documentation can be found here.

How to Calculate the Difference Between Two Rows in SQL [PostgreSQL] – For Aggregated Columns

I have a table like this

CREATE TABLE fake_sales
(
    group_id INT NOT NULL,
    year SMALLINT CHECK(year > 0),
    amount DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY(year, group_id)
);

I want to compare the current year’s sale with the last year’s sale using LAG window function.

Like this:

SELECT
    year,
    SUM(amount) AS annual_sale,
    LAG(annual_sale, 1) OVER (PARTITION BY year ORDER BY year) change_amount
FROM fake_sales
GROUP BY year;

but I can’t get the desired result and I got this error

ERROR: column "annual_sale" does not exist

How to compare a record (row) to previous record for an aggregated column?

Tags:

3

Answers


  1. 0

    You should calculate aggregated SUM column first, then use LAG to calculate the difference.

    SELECT 
    year,
    annual_sale,
    (annual_sale - LAG(annual_sale) OVER (ORDER BY year)) AS change_amount
FROM  
    (SELECT
        year,
        SUM(amount) AS annual_sale
    FROM fake_sales
    GROUP BY year) data;

    See demo here.

    Login or Signup to reply.
  2. 0

    It’s because column annual_sale really doesn’t exist: alias is not allowed in lead. You should use SUM(amount) (or use sub-query).

    Also you don’t need to partition your lag.

    SELECT
    year,
    SUM(amount) AS annual_sale,
    LAG(SUM(amount)) OVER (
        ORDER BY year
    ) - SUM(amount) change_amount
FROM fake_sales
GROUP BY year;

    Fiddle here

    Login or Signup to reply.
  3. 0

    I suggest aggregating by year first, then taking the lag in sales.

    SELECT
    year,
    SUM(amount) AS annual_sales,
    LAG(SUM(amount)) OVER (ORDER BY year) AS prev_year_sales
FROM fake_sales
GROUP BY year
ORDER BY year;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search