CODEWITHSUNDEEP
sqlgoogle-bigquery
Rod
Rod

Reputation: 19

How to Sum values in two different columns from different rows (Row 1 column A + Row 0 Column B)

I have 2 different columns, one for Current_Month, the other Next_Month. They relate to the revenue to be recognised for a payment made by a customer.

e.g. Customer A starts subscription of $20 on 15/06/21. Customer A also end their Subscription in August.

CustomerID Month Curr_Month_Revenue Next_Month_Revenue
A 06.21 $10 $10
A 07.21 $10 $10
A 08.21 $10 $10

I would like to create a new table, which sums the Curr_Month column with the Next_month column of the previous row (month-1), to display the revenues to be recognised for each month.

The table would result in:

CustomerID Month Recognised_Revenue
A 06.21 $10
A 07.21 $20
A 08.21 $20
A 09.21 $10

Not sure how to achieve this in SQL

Upvotes: -1

Views: 108

Answers (1)

Bhavesh Ghodasara
Bhavesh Ghodasara

Reputation: 2071

There are two ways to achieve it.

1- Self-join with joining condition as previous month:

SELECT
      t1.customerid,
      t1.month,
      (t1.Curr_Month_Revenue + t2.Curr_Month_Revenue) Recognised_Revenue
    FROM
      table1 t1
    LEFT OUTER JOIN
      table1 t2
    ON
      t1.customerid = t2.customerid
      AND t1.month = t2.month-1

2- Lag or Lead funtion

lag(Curr_Month_Revenue )over(partition by customerid  order by month) prev_mont_revenue

Upvotes: 1

Related Questions