# How do I fill in data in a current row with data from a preceding row with a non zero value?

## All we need is an easy explanation of the problem, so here it is.

Suppose I have the following table:

``````CREATE TEMPORARY TABLE stocks (
my_date date,
my_shares int,
stock_price decimal,
new_shares int
);

INSERT INTO stocks VALUES('2021-01-01', 100, 10.50, 100);
INSERT INTO stocks VALUES('2021-01-02', 100, 11.00, 0);
INSERT INTO stocks VALUES('2021-01-03', 100, 11.25, 0);
INSERT INTO stocks VALUES('2021-01-04', 150, 12.00, 50);
INSERT INTO stocks VALUES('2021-01-05', 175, 13.00, 25);

select * from stocks
``````

In the table above, `my_shares` is the total number of shares that I own and `new_shares` are the number of shares that I would purchase on `my_date`.

I want to create another column called `my_cost` that shows the total cost of the shares that I most recently purchased (not total). `my_cost` = `stock_price` * `new_shares` when `new_shares` > 0.

The final table should look like this:

Notice how even though I don’t purchase new stocks on 1/2 and 1/3, the cost from my most recent purchase is still shown.

I cannot figure out how to do this. I’ve mainly tried `CASE` with different variations of `rows between unbounded preceding and preceding 1 row` but I’m just guessing. Can anyone help?

PostgreSQL 13

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

``````WITH cte AS (
SELECT *, SUM(new_shares>0) OVER (PARTITION BY my_shares ORDER BY my_date) grp
FROM stocks
)
SELECT *, FIRST_VALUE(stock_price * new_shares) OVER (PARTITION BY my_shares, grp ORDER BY my_date) my_cost
FROM cte
``````

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5af6e1eb3f6e747e47eb8763c2b79f63

### Method 2

After you have fixed the current data, get rid of that code. Then modify your code so that future `INSERTs` grab the previous row in case there are new purchases today. That is, it is a lot cheaper to avoid GIGO.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂