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

Postgresql – How to Get Current And Previous Year Total Amount SUM In Postgres

I have a table like

SR No Name Amount 1 Amount 2 Year
1 Crop1 1000 3000 2023
2 Crop2 2000 1000 2023
3 Crop3 5000 2500 2022
4 Crop4 1500 3500 2022
5 Crop5 3500 4500 2021
6 Crop6 9500 4000 2021

Now, I want Result like

Year Year Sum Previous year Sum
2023 3000 6000
2022 6000 8500
2021 8500 0 
Explanation - Year 2023 -> Year Sum = total of Amount 1 of year 2023
              Year 2023 -> Previous Year Sum = total of amount 2 of year 2022
              Year 2022 -> Year Sum = total of Amount 2 of year 2022(Previous year total of year 2023 row)
              Year 2022 -> Previous Year Sum = total of Amount 2 of year 2021
              Year 2021 -> Year Sum = total of Amount 2 of year 2021(Previous year total of year 2022 row).
              Year 2021 -> Previous Year Sum = total of Amount 2 of year 2020.

I have tried using case when conditions but not getting proper result,

How to get result like this.

Tags:

2

Answers


  1. 0

    You can use a cte and window functions:

    with sums as (select year, sum(amount1) sum1, sum(amount2) sum2
              from test group by year)
SELECT year, 
       case when (ROW_NUMBER() OVER (ORDER BY year desc) = 1) then sum1 else sum2 end as year_sum,
       COALESCE (LEAD(sum2,1) OVER (ORDER BY year DESC) , 0)  as previous_year_sum
FROM
    sums order by year desc;

    db-fiddle

    Login or Signup to reply.
  2. 0

    When the "Year Sum" is the total of "Amount 1" (always), you can do:

    SELECT
  Year,
  "Year Sum",
  COALESCE(Lag("Sum2") OVER (Order by Year),0) as "Previous year Sum"
FROM (
  SELECT
    Year,
    SUM(Amount_1) as "Year Sum",
    SUM(Amount_2) as "Sum2"
  FROM mytable
  GROUP BY Year
)
ORDER BY Year dESC

    output:

    year Year Sum Previous year Sum
    2023 3000 6000
    2022 6500 8500
    2021 13000 0

    First get the totals, then use LAG() to move the totals to the previous year.

    see: DBFIDDLE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search